CODEWITHSUNDEEP
sqloracle-databasejoinrownumright-join
Jakabfi Attila
Jakabfi Attila

Reputation: 367

Oracle ROWNUM with multiple joins

I'm trying to execute a query on an Oracle database.

My table structure is:

I'm using the following query:

SELECT o.id ID, ROWNUM rn                                               /*row 1*/
FROM TABLE_D d
LEFT JOIN TABLE_O o ON d.O_ID=o.ID 
RIGHT JOIN TABLE_R r ON r.O_ID = o.ID AND r.TYPE = 'SOME_TYPE'          /*row 4*/
RIGHT JOIN TABLE_MTM mtm ON o.ID = mtm.ORD_ID                           /*row 5-6*/
RIGHT JOIN TABLE_M m ON mtm.M_ID = m.ID AND m.TYPE = 'SOMETHING_ELSE'   /*row 5-6*/

WHERE o.ACTIVE=0

ORDER BY o.SOME_COL ASC;

(This is just a small part of my full query; yes I do need the ROWNUM, and the 'd' table, and everything)

Can anyone tell me what I'm missing?

Update: All works well on Oracle 12c. Doesn't work correctly on 10g.

Upvotes: 1

Views: 2202

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269953

You are missing a lot of things:

SELECT o.id ID, ROWNUM as rn                                               /*row 1*/
FROM TABLE_D d LEFT JOIN
     TABLE_O o
     ON d.O_ID = o.ID AND o.ACTIVE = 0 LEFT JOIN
     TABLE_R r
     ON r.O_ID = o.ID AND r.TYPE = 'SOME_TYPE' LEFT JOIN         /*row 4*/
     TABLE_MTM mtm
     ON o.ID = mtm.ORD_ID LEFT JOIN                         /*row 5-6*/
     TABLE_M m
     ON mtm.M_ID = m.ID AND m.TYPE = 'SOMETHING_ELSE'   /*row 5-6*/
ORDER BY o.SOME_COL ASC;

First, for a LEFT JOIN, the condition on the second table should be in the ON clause not the WHERE clause. Second, for a RIGHT JOIN, the condition on the first table should be in the ON clause. A condition on the second table (for a RIGHT JOIN) does not filter rows.

In any case, you should not mix LEFT JOIN and RIGHT JOIN in the same query, unless you really know what you are doing and you really need it. I suspect that LEFT JOIN and the above construct should be correct.

Upvotes: 1

Related Questions