Oracle SQL Query (LEFT OUTER JOIN)

Question

I have five tables and i want to get result out of them. Here is what i am doing:

select
        person.SERVICE_NO as Service_No, person.CNIC_NO as CNIC, person.NAME as NAME , card.CPLC_SERIAL_NO as Card_Number,
        child_dc.NAME as Child_DC, root_dc.NAME as Root_DC, person.OU as OU, person.EMAIL as Email
from
        person,card,person_card,child_dc,root_dc
where
        person_card.PERSON_ID = person.ID
and
        person_card.CARD_ID = card.ID
and
         person.CHILD_DC_ID = child_dc.ID
and
         root_dc.ID = child_dc.ID;

This query give redundant values, (not if i place a distinct with it). I was thinking of doing it with left out join; which means that i would be LEFT OUTER JOINING with 5 tables. How would i do this. If anyone has more optimized query or any other idea, that would be great.

reggie · Accepted Answer · 2011-12-08T20:02:34.167

2

Query Updated:

select  distinct
    person.SERVICE_NO as Service_No, 
    person.CNIC_NO as CNIC, person.NAME as NAME , 
    card.CPLC_SERIAL_NO as Card_Number,
    child_dc.NAME as Child_DC, 
    root_dc.NAME as Root_DC, person.OU as OU, 
    person.EMAIL as Email
from

     person_card inner join person
     on person_card.PERSON_ID = person.ID
     inner join card
     on person_card.CARD_ID = card.ID
        left outer join child_dc 
     on person.CHILD_DC_ID = child_dc.ID
        left outer join root_dc
    on child_dc.ID = root_dc.ID;

edited Dec 08 '11 at 20:02

answered Dec 08 '11 at 19:31

reggie

13,313
13
41
57

Try it now. i was missing the on keyword – reggie Dec 08 '11 at 20:02
Just noticed that you forgot to place on in the last line, moreover, if i don't place distinct, its a mess. How would you grade your query in term of optimization with my query, as both are giving the same results. – Femme Fatale Dec 08 '11 at 20:04
if you check the speed of execution, mine will be faster than yours since I am specifying joins and you need the distinct keyword to output distinct values – reggie Dec 08 '11 at 20:05
@ShahJee For more information, you can check this link: http://stackoverflow.com/questions/1018822/inner-join-versus-where-clause-any-difference . Also, since I am using a `left join` on `child_dc` and `root_dc`, it will yield different values as compared to your query when it comes across null values in `child_dc` and `root_dc` – reggie Dec 09 '11 at 13:29

Oracle SQL Query (LEFT OUTER JOIN)

1 Answers1