472,358 Members | 1,955 Online

# Problem with multiple joins.

Hi All Database Gurus,

I am trying to write code which will produce all the possible valid
queries, given tables and join information for tables.
Right now i am just trying to construct all the sequential joins.
eg. if i have 4 tables A, B, C, D and the join conditions are
A Inner join B,
B Inner Join C,
C Left Outer join D
then i am constructing joins as :
1. A IJ B IJ C LJ D
2. B IJ A IJ C LJ D
3. B IJ C IJ A LJ D
4. B IJ C LJ D IJ A
5. C IJ B IJ A LJ D
6. C IJ B LJ D IJ A
7. C LJ D IJ B IJ A
I am not placing any paranthesis to specify the join order. And many
of them are giving me same output.

Can anybody tell me how to detect the joins which will give the same
output ?

here in this case the number of combinations are 7 but for 8 tables i
am getting 420 combinations and many of them are same.

eagerly waiting for suggetions.

Thanking you.

Prem.
(pr*******@hotmail.com)
Jul 20 '05 #1
1 4558
Without knowing the DDL and the join conditions there isn't a simple answer.

For example, these two joins:

....
FROM A
LEFT JOIN B
ON A.x = B.x
INNER JOIN C
ON A.x = C.x
....
FROM A
LEFT JOIN B
ON A.x = B.x
INNER JOIN C
ON B.x = C.x

could produce very different results even though they contain the same
tables and joins in the same sequence.

--
David Portas
------------