<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
</head>
<body bgcolor="#ffffff" text="#000000">
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_attributes using (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_id in<br>
(select people_id from test_attributes where attribute='USA President'
<br>
INTERSECT<br>
select people_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>
and people_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 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.<br>
<br>
Thanks for any references to a solution! -Bryce<br>
</body>
</html>