stUrb stUrb - 4 months ago 15
MySQL Question

Query 1-many relation through multiple wheres from relation

Sorry about the fuzzy title; if somebody has a better alternative I'll change it!

I have two tables one with projects and their meta-data; and one with milestones which the project has reached. A project can have multiple milestones.

projects: id | title | .....
milestones: id | type | project_id | ...


It's pretty simple to retreive all of the projects which have x-type of milestone. Or retrieve the last milestone from each project.

But the problem I have right now is to built a query which gives me all project (id's) which have a given set of milestones.

So: return all projects which have milestone type-a AND type-d AND type-x. Where the number of milestones can be variable; but always >= 1

Anybody a suggestion?

Answer

Assuming projects.id is PK on projects, use group by and having count(*)

select p.id, count(*) milestones
  from projects p join milestones m on p.id = m.project_id
 where m.type in ('A','D','X')
group by p.id
having count(*) >= 1

If you want to have all the 3 types, you should count distinct milestones.type (thanks @jarlh):

select p.id, count(*) milestones
  from projects p join milestones m on p.id = m.project_id
 where m.type in ('A','D','X')
group by p.id
having count(distinct m.type) = 3