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 reggie · Accepted Answer · 2011-12-08T19:31:17

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;

Oracle SQL Query (LEFT OUTER JOIN)

1 Answers