Why do these two queries return different results? Trying to replace select from multiple tables with joins

Lily_Viceroy 0
Viewed 32

I'm trying to rewrite a select from multiple tables into a series of joins. I thought my solution would be equivalent but it's not returning the same data. An ideas? Here's the code:

/* without joins:*/
SELECT
A.column3,
D.column6,
E.column2,
I.column2,
E.column3,
F.column1,
G.column2

FROM table1 A, table2 B, table3  C, table4 D, table5 E, table6 F, table7 G table8 H  
LEFT OUTER JOIN table9 I ON H.ID = I.ID  
WHERE
A.ID = C.ID
B.ID = C.ID
D.ID = B.ID
D.ID = E.ID
E.ID = F.ID
F.ID = G.ID
E.ID = H.ID  
AND H.ISDELETED<>'T'  
AND D.ISDELETED<> 'T'

/* Here's my attempt to turn it into at series of joins: */

SELECT
D.column3,
E.column6,
I.column2,
E.column2,
F.column3,
G.column1  
FROM table1 A  
JOIN table3 C ON A.ID = C.ID  
JOIN table2  B ON B.ID = C.ID  
JOIN table4 D ON D.ID = B.ID  
JOIN table5 E ON D.ID = E.ID  
JOIN table6 F ON E.ID = F.ID  
JOIN table7 G ON F.ID = G.ID  
JOIN table8 H ON E.ID = H.ID  
LEFT OUTER JOIN table9 I ON H.ID = I.ID  
WHERE G.ISDELETED<>'T'  
AND E.ISDELETED<> 'T'
sql
1 Answers

Change your query with the JOIN's to this and see if it matches up with your initial query:

SELECT
A.column3,
D.column6,
E.column2,
I.column2,
E.column3,
F.column1,
G.column2
FROM table1 A  
INNER JOIN table2 B ON A.ID = C.ID  
INNER JOIN table3 C ON B.ID = C.ID  
INNER JOIN table4 D ON D.ID = B.ID  
INNER JOIN table5 E ON D.ID = E.ID  
INNER JOIN table6 F ON E.ID = F.ID  
INNER JOIN table7 G ON F.ID = G.ID  
INNER JOIN table8 H ON E.ID = H.ID  
LEFT OUTER JOIN table9 I ON H.ID = I.ID  
WHERE
AND H.ISDELETED<>'T'  
AND D.ISDELETED<> 'T'

Your aliases do not match between the two queries which may throw off the results of the JOIN.

Also, in your first query your WHERE clause is:

AND table8.ISDELETED<>'T'  
AND table4.ISDELETED<> 'T'

In your bottom query, your WHERE clause is:

AND table7.ISDELETED<>'T'  
AND table5.ISDELETED<> 'T'

Which may also lead to difference results between the two.

GRIV0
Related