PDA

View Full Version : Multiple JOINS without NESTING



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!

mohanvijay
11-09-2011, 09:02 AM
Did you try -- DISTINCTROW