Left Inner Join with linking table on Oracle database

[troyarch]

I am trying to create a left inner join on a linking table to pull back each record in a PERSON table along with its one-and-only correlating "primary" phone number (indicated by a primary_indicator field on the TELEPHONE table). If the PERSON record does not have a "pimary" phone I want to return null values for the phone number. But my resuts only pull back PERSON records that have a "primary" phone. My table structure is as follows (with three tables):

PERSON
- id
- last_name

PERSON_TELEPHONE (this is the linking table)
- person_id
- telephone_id

TELELPHONE
- phone_number
- primary_indicator (where '1' = true)

My sql is as follows:
SELECT A.LAST_NAME,C.PHONE_NUMBER
FROM PERSON A LEFT OUTER JOIN PERSON_TELEPHONE B
ON A.PERSON_ID = B.PERSON_ID
JOIN TELEPHONE C
ON B.TELEPHONE_ID = c.TELEPHONE_ID
WHERE C.PRIMARY_INDICATOR = '1'

I think my problem has something to do with not having an inner join on the C.PRIMARY_INDICATOR column (in old-school sql this would be indicated by (+)). I am not sure how this is done in ANSI though.

I do not have permissions to create a temp table in the database.

 

[PHV]

What about this ?

SELECT A.LAST_NAME,D.PHONE_NUMBER
FROM PERSON A LEFT OUTER JOIN (
SELECT PERSON_ID,PHONE_NUMBER FROM PERSON_TELEPHONE B INNER JOIN TELEPHONE C
ON B.TELEPHONE_ID = C.TELEPHONE_ID WHERE C.PRIMARY_INDICATOR = '1'
) D ON A.PERSON_ID = D.PERSON_ID

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[troyarch]

That is wonderful and the results are exactly what I was hoping to get. Thank you very much.

If you would, I have an additional problem that is directly related. I also need to return the "primary" email address that is associated to the PERSON record, or return a null value if a "primary" email address does not exist.

The additional tables will be as follows:
PERSON_EMAIL_ADDRESS
- person_id
- email_address_id

EMAIL_ADDRESS
- email_id
- email_text
- primary_indicator

I have tried adding the additional outer join to the sql that you sugested with no luck.

 

[troyarch]

I think I just got it. Easier than I thought:

SELECT A.LAST_NAME,D.PHONE_NUMBER
FROM PERSON A LEFT OUTER JOIN (
SELECT PERSON_ID,PHONE_NUMBER FROM PERSON_TELEPHONE B INNER JOIN TELEPHONE C
ON B.TELEPHONE_ID = C.TELEPHONE_ID WHERE C.PRIMARY_INDICATOR = '1'
) D ON A.PERSON_ID = D.PERSON_ID
LEFT OUTER JOIN (
SELECT PERSON_ID,EMAIL_ADDRESS FROM PERSON_EMAIL_ADDRESS D INNER JOIN EMAIL_ADDRESS E
ON D.EMAIL_ADDRESS_ID = E.EMAIL_ADDRESS_ID WHERE E.PRIMARY_INDICATOR = '1'
) F ON A.PERSON_ID = F.PERSON_ID