×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Left Inner Join with linking table on Oracle database

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.

RE: Left Inner Join with linking table on Oracle database

(OP)
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.

RE: Left Inner Join with linking table on Oracle database

(OP)
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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close