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

From: Bryce Nesbitt <bryce2(at)obviously(dot)com>
To: sql pgsql <pgsql-sql(at)postgresql(dot)org>
Subject: Best way to "and" from a one-to-many joined table?
Date: 2008-12-05 18:55:23
Message-ID: 4939791B.5090604@obviously.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

<!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.&nbsp; 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 |&nbsp;&nbsp; attribute&nbsp;&nbsp; |<br>
+-----------+-------------+---------------+<br>
|&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 10 | Satan&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; | The Devil&nbsp;&nbsp;&nbsp;&nbsp; |<br>
|&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 9 | Santa&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; | Imaginary&nbsp;&nbsp;&nbsp;&nbsp; |<br>
|&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 8 | Obamba&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; | Dark Hair&nbsp;&nbsp;&nbsp;&nbsp; |<br>
|&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 8 | Obamba&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; | Dark Hair&nbsp;&nbsp;&nbsp;&nbsp; |<br>
|&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 8 | Obamba&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; | USA President |<br>
|&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 10 | Satan&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; | Dark Hair&nbsp;&nbsp;&nbsp;&nbsp; |<br>
+-----------+-------------+---------------+<br>
<br>
# select person_name from test_people where people_id in<br>
(select people_id from test_attributes where attribute='USA President'&nbsp;
<br>
&nbsp;INTERSECT<br>
&nbsp;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(*) &gt;= 2;</tt><br>
<br>
<br>
A postgres specific solution is OK, but SQL92 is better.&nbsp; 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!&nbsp; -Bryce<br>
</body>
</html>

Attachment Content-Type Size
unknown_filename text/html 2.3 KB

Responses

Browse pgsql-sql by date

  From Date Subject
Next Message Sean Davis 2008-12-05 19:08:20 Re: Aggregates with NaN values
Previous Message Mark Roberts 2008-12-05 18:51:16 Re: Aggregates with NaN values