CODEWITHSUNDEEP
mysql
Nyein Chan
Nyein Chan

Reputation: 1215

How to test whether all values exist in another table?

I have clients table, projects table and issues table. 

clients
cid  cname
-----------
 c1   ca  
 c2   cb
 c3   cc

projects
pid  pname  cid
----------------
 p1   pa     c1
 p2   pb     c1
 p3   pc     c1
 p4   pd     c2
 p5   pe     c2

issues 
iid  iname  pid
----------------
i1     ia   p1
i2     ib   p2
i3     ic   p4
i4     id   p5

I want clients whose projects are all exist in issues. It is c2.
How to write MYSQL query for this?

Upvotes: 1

Views: 31

Answers (2)

agold
agold

Reputation: 6286

The following query should give you the list of client IDs that have all projects listed in the issue table:

SELECT p.cid
FROM projects AS p LEFT JOIN issues AS i ON p.pid=i.pid
GROUP BY p.cid
HAVING COUNT(*)=SUM(CASE WHEN i.iid is NULL THEN 0 ELSE 1 END);

I use a LEFT JOIN such that I get all rows of projects, and if a pid does not exist in issues the values for issues (such as i.iid) will be NULL.

Note: I have updated using the SQL fiddle of Forward.

Upvotes: 2

Blank
Blank

Reputation: 12378

Try to use having clause:

select
    c.cid,
    c.cname
from clients c
left join projects p
on c.cid = p.cid
left join issues i
on p.pid = i.pid
group by c.cid, c.cname
having count(distinct p.pid) = count(distinct i.pid) and count(distinct p.pid) > 0

Check SQLFiddle Demo Here.

Upvotes: 2

Related Questions