Skip site navigation (1) Skip section navigation (2)

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 (view raw or flat)
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: unknown_filename
Description: text/html (2.3 KB)

Responses

pgsql-sql by date

Next:From: Sean DavisDate: 2008-12-05 19:08:20
Subject: Re: Aggregates with NaN values
Previous:From: Mark RobertsDate: 2008-12-05 18:51:16
Subject: Re: Aggregates with NaN values

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group