0

I have two relational tables, person and event_registration, and I would like to select all persons who have registered to 10 or less events. I have found a query which seems to be correct. It can find these persons with the following query:

  SELECT person_id 
    FROM event_registration 
GROUP BY person_id 
  HAVING COUNT(*) <= 10

Note, event_registration.person_id is related to the table row that matches person.id. Now I would like to use this query searching the person table, but my query seems to crash mysql. Why?

SELECT id 
  FROM person 
 WHERE id IN (
       SELECT person_id 
         FROM event_registration 
     GROUP BY person_id 
       HAVING COUNT(*) <= 10
 )

The reason I would like to change the query is because I also look for other properties, so the (more close to) final query will look something like:

SELECT * 
  FROM person 
 WHERE name LIKE '%something%' 
   AND AGE > 20 
   AND id IN (
       SELECT person_id 
         FROM event_registration 
     GROUP BY person_id 
       HAVING COUNT(*) <= 10
 )

Of course this doesn't work, as mysql seems to crash. What am I doing wrong?

Ian Gregory
  • 5,770
  • 1
  • 29
  • 42
Robin Manoli
  • 2,162
  • 2
  • 25
  • 30

1 Answers1

0
SELECT distinct id FROM person 
WHERE id IN
    (SELECT person_id 
     FROM event_registration 
     GROUP BY person_id HAVING COUNT(*) <= 10)
Michael Durrant
  • 93,410
  • 97
  • 333
  • 497
  • Then the `distinct`, to do the counts just once for each distinct person, probably made the difference. – Michael Durrant Dec 26 '12 at 20:01
  • I don't understand however, that finding the IDs without a subquery would take a second, but then displaying the values along with other values would make the query take over 2 minutes... – Robin Manoli Dec 26 '12 at 20:32