Left Inner Join with linking table on Oracle database
Left Inner Join with linking table on Oracle database
(OP)
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.
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.
Talk To Other Members
RE: Left Inner Join with linking table on Oracle database
CODE
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: How Do I Get Great Answers To my Tek-Tips Questions?
FAQ181-2886: How can I maximize my chances of getting an answer?
RE: Left Inner Join with linking table on Oracle database
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.
RE: Left Inner Join with linking table on Oracle database
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