CODEWITHSUNDEEP
mysqlsql
Leze
Leze

Reputation: 759

SELECT records from two table

I have two tables :

PEOPLE

ID        |NAME       
|A2112    |John       
|B3200    |Mary       
|C2454    |Bob        
|F2256    |Joe

JOBS

|ID        |NAME          |PEOPLE 
|56565     |Taxi Driver   |A2112
|23232     |Herborist     |A2112  
|12125     |Jumper        |B3200  
|25425     |Taxi Driver   |C2454  
|12456     |Taxi Driver   |F2256  
|56988     |Herborist     |F2256  
|45459     |Superhero     |F2256

I wonder how I can select any records FROM People that have JOBS ID 56565 AND 23232 in performant way.

The search pattern may be two or multiples jobs, and the records can have another jobs too.

So the result will be John and Joe in this example.

Upvotes: 1

Views: 81

Answers (4)

Valery Viktorovsky
Valery Viktorovsky

Reputation: 6736

The INNER JOIN keyword selects all rows from both tables as long as there is a match between the columns in both tables.

SELECT
    p.NAME, COUNT(*) as tot
FROM
    PEOPLE p 
        INNER JOIN JOBS j ON (p.ID = j.PEOPLE)
WHERE
    j.ID IN (56565, 23232)
GROUP BY
    p.NAME
HAVING
    COUNT(*) > 1

Visual explanation of INNER JOIN bellow:

Visual explanation of INNER JOIN

Upvotes: 4

jarlh
jarlh

Reputation: 44795

Not quite sure if I got you right. This will return people who have job 56565 and/or 23232:

select distinct p.name
from people p
  join jobs j on p.id = j.peopleid
where j.id in (56565, 23232)

If BOTH jobs are required:

select p.name
from people p
  join jobs j on p.id = j.peopleid
where j.id in (56565, 23232)
group by p.name
having count(*) > 1

The HAVING clause can also be written as

having max(j.id) <> min(j.id)

Perhaps better performance that way.

Upvotes: 6

Thorsten Kettner
Thorsten Kettner

Reputation: 95101

"People that have JOBS" translates to WHERE EXISTS in SQL:

select *
from people
where exists
(
  select *
  from jobs
  where jobs.people = people.id
  and jobs.id in (56565, 23232)
);

This can also be written with an IN clause, which I even consider slightly more readable for its simplicity:

select *
from people
where id in
(
  select people
  from jobs
  where id in (56565, 23232)
);

Upvotes: 0

balaraman
balaraman

Reputation: 413

    SELECT * FROM PEOPLE as p
    JOIN JOB as j ON j.PEOPLE = p.ID
    WHERE j.ID IN(56565, 23232)

Upvotes: 0

Related Questions