Looking for 0-n rows of varying types on an existing list of people

I've got a people table with unique ID 'id'. Activities performed by those people are stored in a people_activity table, with columns 'type' (activity type, an integer) and 'id', which matches the person. I have a query where I pull back many people at a time, but I'd like to add to that query the conditions that a person does or does not have 0 or more activities performed.

If I was querying a single person, it would be a simple "where people_activity.type = 4 and people_activity.type <> 12", etc., but since I'm pulling back many people, I'm not quite sure how to do it.

My current query, with a bad where clause for the type (apologies, I simplified it in my explanation):

select first , middle , last , y.dob , rid.rid as rid , rid.record_number
            from    (select first, middle, last, email, added, phone, a.revision as revision, type, lastupdated, a.rid as rid from people a inner joi开发者_C百科n (select people.rid, max(revision) as revision from people group by people.rid) b on a.rid = b.rid and a.revision = b.revision) p inner join youth y on p.rid = y.rid
                    inner join language l on y.language_t = l.language_id
                    inner join cases on y.case_id = cases.id
                    inner join race r on y.race_t = r.race_id
                    inner join providers_r cp on y.provider_id = cp.provider_id
                    inner join rid on y.rid = rid.rid
            where   p.first like "c%" and p.middle like "%" and p.last like "%" and  exists (select * from youth_activity where type = 2)
            group by y.rid
            order by last asc

You'll see my current way of doing it, "where exists (select * from youth_activity where type=4)" is no good because that simply checks that a single type of 4 exists at all, and not necessarily for the specific people returned in the query.

MySQL has some performance issues with EXISTS, but syntactically, you would correlate the EXISTS subquery with the main query. But if you want has completed activities 1,2,3, not 4 and 8, then

            where   p.first like "c%" and p.middle like "%" and p.last like "%"
            and (select count(distinct a.type) from youth_activity a where a.type in (1,2,3) and a.youthid = y.rid) = 3
            and not exists (select * from youth_activity a where a.type in (4,8) and a.youthid = y.rid)

I'm not totally clear from your query, but it sounds like you want to do something like this

SELECT COUNT(*),p.* FROM Person p
  LEFT OUTER JOIN Person_Activity pa ON p.id = pa.id 
  WHERE (whatever your condition)
  GROUP BY p.id
  HAVING COUNT(*)> (number of activities) 

If you want to include persons with no activities, you'll manually have to check whether pa.id is NULL.

Here's my approach based on the comment

-- Get the list of all activities of interest (call this QUERY X). The full outer produces a list of all Person/Activity pairs, the left outer determines whether they have been done

 SELECT p.id, act.activity_id, CASE WHEN pa.id is NULL THEN 1 ELSE 0 END has_done FROM Person p
      FULL OUTER JOIN (SELECT DISTINCT activity_id FROM Person_Activity) act a
      LEFT OUTER JOIN Person_Activity on a.activity_id = pa.activity_id and pa.id = p.id
      WHERE pa.id IN (x,y,z)

 -- Either generate the SQL based on what you need 
 SELECT * FROM (QUERY X) WHERE (activity.id = 1 and done = 0 ... )

 -- Or use some aggregate function to generate a summary you can compare to an input value.  CONCAT here appends the strings, you'll have to figure out this one for your dialenct
 SELECT p.id FROM (QUERY X) GROUP BY p.id HAVING CONCAT(pa.id + '/' + done + ',') = '1/1,2/0....'