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!
Reply With Quote
