[SQL] Best way to "and" from a one-to-many joined table?

[Bryce Nesbitt]

Dear Experts, I'm looking for a good technique to do "and" searches on one-to-many joined tables.  For example, to find people with both 'dark hair' and 'president': # select * from test_people join test_attributes using (people_id); +-----------+-------------+---------------+ | people_id | person_name |   attribute   | +-----------+-------------+---------------+ |        10 | Satan       | The Devil     | |         9 | Santa       | Imaginary     | |         8 | Obamba      | Dark Hair     | |         8 | Obamba      | Dark Hair     | |         8 | Obamba      | USA President | |        10 | Satan       | Dark Hair     | +-----------+-------------+---------------+ # select person_name from test_people where people_id in (select people_id from test_attributes where attribute='USA President'   INTERSECT  select people_id from test_attributes where attribute='Dark Hair'); # select person_name from test_people where people_id in (select people_id from test_attributes where attribute='USA President') and people_id in (select people_id from test_attributes where attribute='Dark Hair'); # select people_id,count(*) as count from test_people join test_attributes using (people_id) where attribute='Dark Hair' or attribute='USA President' group by people_id having count(*) >= 2; A postgres specific solution is OK, but SQL92 is better.  I had the "in" solution recommended to me, but it's performing dramatically poorly on huge tables. Thanks for any references to a solution!  -Bryce