SQL query construction

[mm0000]

How to construct a SQL query. I have 2 tables
Table1
Col1 col2 col3
A B 5
A B 5
Table2
Col1 col2 col3 col4 other columns...
A B 5 A1
A B 6 A1
A B 5 A2
A B 5 A2
A B 7 A3
A B 5 A3
.....
.....
I want to select all rows from table 2 which equal the values of table 1, however the rows should be selected only if they have the same value in col4. A query with table1.col1 = table2.col1 and table1.col2=table2.col2 and table1.col3 = table2.col3 will give the results
A B 5 A1
A B 6 A1
A B 5 A2
A B 5 A3

However what I require are only the first 2 rows since only these 2 rows have the same value in column 4('A1')

thanks in advance

 

[mm0000]

Correction to my post...sorry....

How to construct a SQL query. I have 2 tables
Table1
Col1 col2 col3
A B 5
A B1 5
Table2
Col1 col2 col3 col4 other columns...
A B 5 A1
A B1 6 A1
A B 5 A2
A B1 5 A2
A B 7 A3
A B1 5 A3
.....
.....
I want to select all rows from table 2 which equal the values of table 1, however the rows should be selected only if they have the same value in col4. A query with table1.col1 = table2.col1 and table1.col2=table2.col2 and table1.col3 = table2.col3 will give the results
A B 5 A1
A B 5 A2
A B1 5 A2
A B1 5 A3

However what I require are only the 2nd and 3rd rows since only these 2 rows have the same value in column 4('A2')

thanks in advance again..

 

[danceman]

select * ;
from table1 a, table1 b; && a self join
on a.col4 = b.col4;
where table1.col1 = table2.col1 and;
table1.col2=table2.col2 and;
table1.col3 = table2.col3 Attitude is Everything

 

[mm0000]

Tried it but the sql statment gives an error 'Command contains unrecognised phrase /keyword.

The statement;
select a.col1,a.col2,a.col3,b.col4 ;
from temp a, temp1 b ;
on a.col4 = b.col4 ;
where a.col1 = b.col1 and ;
a.col2=b.col2 and ;
a.col3 = b.col3

The error is in the 'on a.col4 = b.col4' section for if it is removed the statement executes.

 

[danceman]

the table names do not match

The statement;
select a.col1,a.col2,a.col3,b.col4 ;

from temp a, temp1 b ;
temp & you have to different tables here
on a.col4 = b.col4 ;
where a.col1 = b.col1 and ;
a.col2=b.col2 and ;
a.col3 = b.col3
Attitude is Everything

 

[DSummZZZ]

You also cannot use comments in the middle of the statement.
...
from table1 a, table1 b; && a self join
on a.col4 = b.col4;...

Dave S.

 

[rgbean]

Dave,
Actually you CAN have comments (at least the && type) inside a multi-line statement - it's worked since at least VFP 5.0!

Rick

 

[danceman]

Thanks for pointing this out.
My comment is only ment to point out the change to make, not for actual code to run. Attitude is Everything

 

[DSummZZZ]

Hmm. I have had problems with it before so I just avoid it now.

?'this ' + ; &&.. comment
' is a ' + ;
' test'

Dave S.

 

[rgbean]

Dave,
While that construct doesn't work in the command window (in VFP 5/6/7) when you just press Enter on one of the three lines, it does work fine in if you highlight all three lines and press Enter. It also works just fine in a a .PRG file in all of them. (I just verified this, so I wouldn't be "guessing".)

This is obviously one of those times when syntax is partially dependent on how you &quot;enter&quot; it! <g>

Rick

 

[DSummZZZ]

Oh lordy what a pun!

Dave S.

 

[mm0000]

danceman,
I didnt understand what you meant by:

temp & you have to different tables here

MM

 

[danceman]

I hope to explain

select * ;

BELOW IS WHAT IS CALLED A SELF JOIN. I AM DEFINING TABLE1 AS TWO TABLES a AND b. I made the mistake of the wrong table. change table1 to table2

OLD
from table1 a, table1 b; && a self join
on a.col4 = b.col4;
NEW
from table2 a, table2 b; && a self join
on a.col4 = b.col4;

where table1.col1 = table2.col1 and;
table1.col2=table2.col2 and;
table1.col3 = table2.col3

the WHERE is the same as you had. I have not tested this, but check out documentation on self joins on mocrosoft knowledge base. Attitude is Everything