SQL - Inner Join

[carolineon]

i had 3 tables that are linked together by several primary keys.
-table1 has A and B as primary key
-table2 has A, B, C and D as primary key
-table3 has A, B and C as primary key
-table1 is linked to table2 by A and B
-table2 is linked to table3 by A, B, C and D
my SQL inner join statement is :

"SELECT * from " & _
"(table1 INNER JOIN " & _
"(table2 INNER JOIN table3 " & _
"ON table2.A=table3.A " & _
"and table2.B=table3.B " & _
"and table2.C=table3.C " & _
"and table2.D=table3.D) " & _
"ON table1.A=table2.A " & _
"and table1.B=table2.B)"

Is this SQL statement true?

Prompt reply is much appreciated.
Thank You.

[Cakkie]

I'm not sure about it, I always use this syntax, also, you need to specify of what table you want to select everything.

SELECT Table1.*
FROM (
Tabel1 INNER JOIN Tabel2 on (
Tabel1.b = Tabel2.b
) AND (
Tabel1.a = Tabel2.a
)
) INNER JOIN Tabel3 on (
Tabel2.d = Tabel3.d
) AND (
Tabel2.c = Tabel3.c
) AND (
Tabel2.b = Tabel3.b
) AND (
Tabel2.a = Tabel3.a
)




Tom Cannaerts
slisse@planetinternet.be

Programming today is a race between software engineers striving to build bigger and better idiot-proof programs, and the universe trying to produce bigger and better idiots. So far, the universe is winning -- Rich Cook

[carolineon]

Thanks Cakkie, i had tried your syntax and it's the same as mine. By the way, what do you mean by the table which i want to select everything? Can you please explain.
Thank you.

[Cakkie]

You say 'select * from (multiple joined tables)'. In your case, I think you will get everything of the first table mantioned in the select statement. My remark was pointed to the *, you should also include a table name there, the make sure not to get unwanted results. You really must do this when you have tables containing the same column names.

Tom Cannaerts
slisse@planetinternet.be

Programming today is a race between software engineers striving to build bigger and better idiot-proof programs, and the universe trying to produce bigger and better idiots. So far, the universe is winning -- Rich Cook

[carolineon]

i think i understand what you mean but i NOT only want data from the first table mention in the Select statement but also data from other table as well. so i dun need to include the table name before the *.is it true?
Thank you

[michi]

For joined table, you can't use * to select All fields on All tables, since some of them are joined. The only way I can think of for now is choose one table as a primary table, and use * on it, but for other tables, type the exact field names one by one. Make sure not to include the same fields as in the first primary table. For example:

select Table2.*, Table1.otherfield1, table1.otherfield2, ..., table3.otherfieldn
from ....



Regards,

Michi

[Raptors Fan]

You say that Table2 has ABCD as Primary Keys and that Table3 has ABC as Primary Keys, and then you say that Table2 is linked to Table3 by ABCD.... but Table3 doesn't have D as a Primary Key.

Assuming that Table2 is linked to Table3 by ABC then:

SELECT Table1.*, Table2.*, Table3.*
FROM (Table1 INNER JOIN Table2 ON (Table1.B = Table2.B) AND (Table1.A = Table2.A)) INNER JOIN Table3 ON (Table2.C = Table3.C) AND (Table2.B = Table3.B) AND (Table2.A = Table3.A);

Now, the above SQL I got from ACCESS. Usually, if the SQL is going to be complex, you should try making the tables in an ACCESS database (if you are not using it) and set the primary keys and links. I find these useful when doing LEFT and RIGHT JOINS.

Hope it helps,

RF