MySQL query with multiple joins

Question

I'd like to get the following information:

prisoner_id, first_name, last_name, job_description for all prisoners, even if they don't have a job

From the tables:

Work:
Prisoner_id
job_id
start_date

Job:
job_id
job_description

Prisoner:
Prisoner_id
person_ssn

Person:
person_ssn
first_name
last_name

Here's my attempt at it, but I don't know how to get the job description.

select prisoner_id, first_name, last_name, job_description from
prisoner
left join work on prisoner_id
natural join person on person_ssn;

Kickstart · Accepted Answer · 2014-05-14 12:30:42Z

1

Assuming that a prisoner always has a record on the person table:-

SELECT prisoner.prisoner_id, 
    person.first_name, 
    person.last_name, 
    job.job_description 
FROM prisoner
INNER JOIN person on prisoner.person_ssn = person.person_ssn
LEFT OUTER JOIN work on prisoner.prisoner_id = work.prisoner_id
LEFT OUTER JOIN job ON work.job_id = job.job_id;

Avoiding using natural joins.

answered May 14, 2014 at 12:30

Kickstart

21.5k2 gold badges26 silver badges33 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Talen Kylon Over a year ago

Thank you very much, this works great. Question, why avoid natural joins?

Rahul Over a year ago

@FatalProphet, you can as well use natural joins (NJ is equivalent to inner join on the same column name).

Kickstart Over a year ago

What happens if someone adds a column to one table that has the same name as a column in another table that a query uses a natural join to join them together? For example say you had a table of parents and a table of children each containing a column for address id. Easy to find parents / children who live in the same house with a natural join. Them someone adds a column for (say) date of birth to each table. Suddenly the natural join is trying to join where there is a match on address and date of birth.

Kickstart Over a year ago

Or a better example. Say someone added a column to your prisoner table for when their sentence started, and decided to call that column start_date. A natural join between prisoner and work that would have worked fine joining on prisoner_id will now fail by trying to join on prisoner_id AND start_date.

Rahul · Accepted Answer · 2014-05-14 12:29:19Z

1

Try this

select pr.Prisoner_id,
p.first_name,
p.last_name,
j.job_description
from prisoner pr
left join work w on pr.Prisoner_id = w.Prisoner_id
left join job j on w.job_id = j.job_id
inner join person p on pr.person_ssn = p.person_ssn

answered May 14, 2014 at 12:29

Rahul

78.1k14 gold badges80 silver badges132 bronze badges

Comments

Kanishk Dudeja · Accepted Answer · 2014-05-14 12:27:00Z

0

select prisoner_id, first_name, last_name, job_description from
prisoner left join work on work.prisoner_id = prisoner.prisoner_id    
natural join person on person.person_ssn = prisoner.person_ssn
natural join job on work.job_id =job.job_id

answered May 14, 2014 at 12:27

Kanishk Dudeja

1,2213 gold badges17 silver badges34 bronze badges

Comments

Ravi Dhoriya ツ · Accepted Answer · 2014-05-14 12:27:20Z

0

Try this,

select p.prisoner_id, per.first_name, per.last_name, j.job_description
from person per, prisoner p, work w left join job on w.job_id=job.job_id
where w.prisoner_id=p.prisoner_id and per.person_ssn=p.person_ssn

Left join between work table and job table
Inner join between work and prisoner
Inner join between person and prisoner

answered May 14, 2014 at 12:27

Ravi Dhoriya ツ

4,4148 gold badges39 silver badges48 bronze badges

Comments

mituw16 · Accepted Answer · 2014-05-14 12:27:38Z

0

Try this..

select prisoner_id, first_name, last_name, job_description from prisoner
join person on person.person_ssn = prisoner.person_ssn
left join work on work.prisoner_id = prisoner.prisoner_id
left join job on job.job_id = work.job_id

answered May 14, 2014 at 12:27

mituw16

5,2784 gold badges31 silver badges56 bronze badges

Comments

Undo · Accepted Answer · 2014-05-14 12:53:41Z

0

Try this:

SELECT w.prisoner_id, p.first_name, p.last_name, j.job_description FROM WORK w LEFT JOIN 
prisoner pr ON w.prisoner_id=pr.prisoner_id LEFT JOIN person p ON    
p.person_ssn=pr.person_ssn
LEFT JOIN Job j ON w.job_id=j.job_id

edited May 14, 2014 at 12:53

Undo♦

25.7k38 gold badges114 silver badges132 bronze badges

answered May 14, 2014 at 12:26

ravikumar

9031 gold badge8 silver badges12 bronze badges

2 Comments

Patrick Q Over a year ago

It's usually helpful if you provide some context or explanation along with your answer.

ravikumar Over a year ago

@PatrickQ thanks for your suggestion.In future ll improve my answers by providing the explanation...

Collectives™ on Stack Overflow

MySQL query with multiple joins

6 Answers 6

4 Comments

Comments

Comments

Comments

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

6 Answers 6

4 Comments

Comments

Comments

Comments

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related