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