Johann
11-08-2011, 12:46 AM
I've searched everywhere on the net but can't find the answer I'm looking for. I have frequently used nested joins that work like this:
SELECT fields FROM tbl1 INNER JOIN (tbl2 INNER JOIN (tbl3 ON tbl3.field3 = tbl1.field1) ON tbl11.field1 = tbl2.field2;
This works where table1 relates to table2, which relates to table3 etc.
Now, I'm looking to create a query where table1 relates to table2, AND table1 relates to table3 etc.
I've used the following code:
"SELECT * FROM ((Tbl1 " _
' & "INNER JOIN Tbl2 ON Tbl2.Field3 = Tbl1.Field1) " _
' & "INNER JOIN Tbl3 ON Tbl3.Field3 = Tbl1.1) [this is followed by several WHERE statement]
This code works BUT, I get duplicate records from Table1 (where it matches table2 and again where it matches table3.) No matter if I add DISTINCT to my SELECT statement, the problem persists.
Is it even possible to perform such a query? I am able to do what I want using a temporary table and SELECT INTO statements followed by loops and criteria checking. However, this makes runtime too long.
Thanks in advance!
SELECT fields FROM tbl1 INNER JOIN (tbl2 INNER JOIN (tbl3 ON tbl3.field3 = tbl1.field1) ON tbl11.field1 = tbl2.field2;
This works where table1 relates to table2, which relates to table3 etc.
Now, I'm looking to create a query where table1 relates to table2, AND table1 relates to table3 etc.
I've used the following code:
"SELECT * FROM ((Tbl1 " _
' & "INNER JOIN Tbl2 ON Tbl2.Field3 = Tbl1.Field1) " _
' & "INNER JOIN Tbl3 ON Tbl3.Field3 = Tbl1.1) [this is followed by several WHERE statement]
This code works BUT, I get duplicate records from Table1 (where it matches table2 and again where it matches table3.) No matter if I add DISTINCT to my SELECT statement, the problem persists.
Is it even possible to perform such a query? I am able to do what I want using a temporary table and SELECT INTO statements followed by loops and criteria checking. However, this makes runtime too long.
Thanks in advance!