CODEWITHSUNDEEP
mysqljoin
Smudger
Smudger

Reputation: 10791

MySQL Query - join tables to show all values from one table and only matching results in other column

Apologies for the poor question title.

I have two tables, jobs and Persons-Jobs.

Jobs Table

1    Painting

2    Plumbing

3    Executive

4    CraneOperator

Persons-Jobs Table

JohnSmit   Painting

JohnSmit   CraneOperator

TomJones   Executive

BradPit    Plumbing

Question

I want to run a query that returns two columns. The first column must show ALL the records from the jobs table. the second column must show the name of the person that does the job. I want a where clause that returns only results for one user, in this example JohnSmit. as an example the query should output:

    Painting   JohnSmit

    Plumbing   *NULL*

    Executive  *NULL*

CraneOperator  JohnSmit

I have tried the outer join but it fails where there are multiple persons in the Persons-Jobs table.

select j.job,p.person 
from jobs j 
LEFT OUTER JOIN `Persons-Jobs` p on j.job = p.job
where p.person='JohnSmit' 
or p.person is NULL

any help,as always would be appreciated.

Thanks and regards, Smudger

Upvotes: 1

Views: 3907

Answers (1)

Don Kirkby
Don Kirkby

Reputation: 56660

You can put the restriction on the person in the join clause. That way, the other people will get filtered out before the join happens.

select j.job,p.person 
from   jobs j 
LEFT 
JOIN   `Persons-Jobs` p 
on     j.job = p.job 
and    p.person='JohnSmit'

Upvotes: 4

Related Questions