CODEWITHSUNDEEP
sqldatabaseoracle11g
Brian Wilson
Brian Wilson

Reputation: 135

Join 3 tables, only use data from 2?

I have 3 tables. I need info from 2 of the tables, while the 3rd table links them. Can someone give me an example of the joins to use? for this example, I need insured’s first and last names, the effective date and the expiration date of their policies.

Mortal table

   SQL> desc mortal
 Name
 ---------------------
 MORTAL_ID
 SEX_TYPE_CODE
 FIRST_NAME
 LAST_NAME
 DOB
 MARITAL_STATUS_CODE
 SSN
 MIDDLE_NAME
 WORK_PHONE

Insured (linking) table

SQL> desc insured    
 Name
 ------------------------
 INSURED_ID
 INSURED_TYPE_CODE
 POLICY_ID
 MORTAL_ID
 BANK_ACCOUNT_NUM
 INSURED_NUM

Policy table

 SQL> desc policy
     Name
     ---------------------------
     POLICY_ID
     PLAN_ID
     POLICY_STATUS_TYPE_CODE
     PAYER_GROUP_ID
     EFFECTIVE_DATE
     POLICY_NUM
     EXPIRE_DATE

As you can seeI need data from tables 1 & 3 , but must use table 2 to link them. what type of join is this? How do I use them?

Upvotes: 1

Views: 113

Answers (3)

user1135379
user1135379

Reputation: 136

You simply need to join the tables. The type of join is depended on what you are trying to acheive. Below is a basic example:

SELECT a.field, c.field 
FROM [Mortal Table] a 
JOIN [Insured Table] b ON a.MORTAL_ID=b.MORTAL_ID 
JOIN [Policy Table] c on b.POLICY_ID=c.POLICY_ID 
*WHERE Clause*

Hope this helps.

Upvotes: 1

MJB
MJB

Reputation: 7686

This is still an easy join. Here is one way. 

select m.first_name, m.last_name, p.effective_date, p.expired_date
from mortal m
  inner join policy p
    on p.policy_id = i.policy_id
  inner join insured i
    on i.mortal_id = m.mortal_id

Not sure what the problem is. But if the question is just "can you join a table you don't select from," then the answer is yes.

Upvotes: 1

JustinHui
JustinHui

Reputation: 729

Something like this?

SELECT mortal.*, policy.*
FROM mortal 
INNER JOIN insured ON insured.MORTAL_ID = mortal.MORTAL_ID
INNER JOIN policy ON insured.POLICY_ID = policy .POLICY_ID

You can replace the inner joins with left joins to see records for every mortal.

Upvotes: 1

Related Questions