Dear Experts,<br /><br /> 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':<br /><br /><tt># select * from test_people join
test_attributesusing (people_id);<br /> +-----------+-------------+---------------+<br /> | people_id | person_name |
attribute |<br /> +-----------+-------------+---------------+<br /> | 10 | Satan | The Devil |<br />
| 9 | Santa | Imaginary |<br /> | 8 | Obamba | Dark Hair |<br /> | 8 |
Obamba | Dark Hair |<br /> | 8 | Obamba | USA President |<br /> | 10 | Satan | Dark
Hair |<br /> +-----------+-------------+---------------+<br /><br /> # select person_name from test_people where
people_idin<br /> (select people_id from test_attributes where attribute='USA President' <br /> INTERSECT<br />
selectpeople_id from test_attributes where attribute='Dark Hair');<br /><br /> # select person_name from
test_people<br/> where people_id in<br /> (select people_id from test_attributes where attribute='USA President')<br />
andpeople_id in<br /> (select people_id from test_attributes where attribute='Dark Hair');<br /><br /> # select
people_id,count(*)as count from test_people<br /> join test_attributes using (people_id)<br /> where attribute='Dark
Hair'or attribute='USA President'<br /> group by people_id having count(*) >= 2;</tt><br /><br /><br /> A postgres
specificsolution is OK, but SQL92 is better. I had the "in" solution recommended to me, but it's performing
dramaticallypoorly on huge tables.<br /><br /> Thanks for any references to a solution! -Bryce<br />
