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

Re: 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>
Cc: Oliveiros Cristina <oliveiros(dot)cristina(at)marktest(dot)pt>
Subject: Re: Best way to "and" from a one-to-many joined table?
Date: 2008-12-05 20:52:41
Message-ID: 49399499.9030608@obviously.com (view raw or flat)
Thread:
Lists: pgsql-sql
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
  <meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
  <title></title>
</head>
<body bgcolor="#ffffff" text="#000000">
It works (with a DISTINCT clause added because of the duplicated row
for Obama).&nbsp; It has a nice clean looking explain plan.&nbsp; It has the
slowest execution time on this sample table (though that might not mean
anything).<br>
<br>
SELECT<br>
DISTINCT<br>
person_name<br>
FROM test_people p<br>
JOIN test_attributes a<br>
ON ((a.people_id = p.people_id) AND (a."attribute" = 'Dark Hair'))<br>
JOIN test_attributes b<br>
ON ((b."people_id" = p."people_id") AND (b."attribute" = 'USA
President'));<br>
<br>
Here's the full test table<br>
<br>
$ pg_dump --table=test_people --table=test_attributes -p 5433 -i<br>
CREATE TABLE test_attributes (<br>
&nbsp;&nbsp;&nbsp; people_id integer,<br>
&nbsp;&nbsp;&nbsp; attribute text<br>
);<br>
COPY test_attributes (people_id, attribute) FROM stdin;<br>
10&nbsp;&nbsp;&nbsp; The Devil<br>
9&nbsp;&nbsp;&nbsp; Imaginary<br>
8&nbsp;&nbsp;&nbsp; Dark Hair<br>
8&nbsp;&nbsp;&nbsp; Dark Hair<br>
8&nbsp;&nbsp;&nbsp; USA President<br>
10&nbsp;&nbsp;&nbsp; Dark Hair<br>
\.<br>
<br>
CREATE TABLE test_people (<br>
&nbsp;&nbsp;&nbsp; people_id integer DEFAULT nextval('test_sequence'::regclass) NOT
NULL,<br>
&nbsp;&nbsp;&nbsp; person_name text<br>
);<br>
COPY test_people (people_id, person_name) FROM stdin;<br>
8&nbsp;&nbsp;&nbsp; Obamba<br>
9&nbsp;&nbsp;&nbsp; Santa<br>
10&nbsp;&nbsp;&nbsp; Satan<br>
\.<br>
<br>
<br>
Oliveiros Cristina wrote:
<blockquote
 cite="mid:00d401c9570e$f1fbcee0$ec5a3d0a(at)marktestcr(dot)marktest(dot)pt"
 type="cite">
  <meta http-equiv="Content-Type" content="text/html; ">
  <meta content="MSHTML 6.00.6000.16735" name="GENERATOR">
  <style></style>
  <div><font face="Arial" size="2">Howdy, Bryce</font></div>
  <div><font face="Arial" size="2">Could you please try this out and
tell me if it gave what you want.</font></div>
  <div><font face="Arial" size="2">Best,</font></div>
  <div><font face="Arial" size="2">Oliveiros</font></div>
  <div>&nbsp;</div>
  <div><font face="Arial" size="2">SELECT person_name<br>
FROM test_people p<br>
JOIN test_attributes a<br>
ON ((a.people_id =&nbsp;p.people_id) AND (a."attribute" = @firstAttr))<br>
JOIN test_attributes b<br>
ON ((b."people_id" = p."people_id") AND (b."attribute" = @secondAttr));</font></div>
</blockquote>
<br>
<br>
</body>
</html>


Attachment: unknown_filename
Description: text/html (2.4 KB)

In response to

Responses

pgsql-sql by date

Next:From: Oliveiros CristinaDate: 2008-12-05 22:38:25
Subject: Re: Best way to "and" from a one-to-many joined table?
Previous:From: Milan OparnicaDate: 2008-12-05 20:16:11
Subject: Re: Best way to "and" from a one-to-many joined table?

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