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

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

From: "Oliveiros Cristina" <oliveiros(dot)cristina(at)gmail(dot)com>
To: "Bryce Nesbitt" <bryce2(at)obviously(dot)com>
Cc: "sql pgsql" <pgsql-sql(at)postgresql(dot)org>, "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 22:38:25
Message-ID: f54607780812051438h2cce6d72ye7167b3a62b30521@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-sql
Hello, Bryce.
It wasn't supposed to output duplicates.

I have assumed that on the test_attributes u didn't have duplicate records,
i.e.,
you didn't have the same pair (people_id, attribute) more than once... But
it seems you do...
And Hence the duplicate row for Obama .
Why is that?
One person can have exactly the same attribute twice?? :-)

On the execution speed, I do declare that query optimization is an area
I know very little about (just to avoid  saying that i know nothing :p ) ,
maybe someone
with more knowledge than me can help you better, but from my
own experience, not just with postgres, but also with other sgbd ,
I can tell that subqueries of the kind WHERE x in (SELECT ... )
have the tendency to be slow, that's why I tried to provide you
a solution with the JOINs


Best,
Oliveiros


2008/12/5 Bryce Nesbitt <bryce2(at)obviously(dot)com>

>  It works (with a DISTINCT clause added because of the duplicated row for
> Obama).  It has a nice clean looking explain plan.  It has the slowest
> execution time on this sample table (though that might not mean anything).
>
> SELECT
> DISTINCT
> person_name
> FROM test_people p
> JOIN test_attributes a
> ON ((a.people_id = p.people_id) AND (a."attribute" = 'Dark Hair'))
> JOIN test_attributes b
> ON ((b."people_id" = p."people_id") AND (b."attribute" = 'USA President'));
>
> Here's the full test table
>
> $ pg_dump --table=test_people --table=test_attributes -p 5433 -i
> CREATE TABLE test_attributes (
>     people_id integer,
>     attribute text
> );
> COPY test_attributes (people_id, attribute) FROM stdin;
> 10    The Devil
> 9    Imaginary
> 8    Dark Hair
> 8    Dark Hair
> 8    USA President
> 10    Dark Hair
> \.
>
> CREATE TABLE test_people (
>     people_id integer DEFAULT nextval('test_sequence'::regclass) NOT NULL,
>     person_name text
> );
> COPY test_people (people_id, person_name) FROM stdin;
> 8    Obamba
> 9    Santa
> 10    Satan
> \.
>
>
> Oliveiros Cristina wrote:
>
> Howdy, Bryce
> Could you please try this out and tell me if it gave what you want.
> Best,
> Oliveiros
>
> SELECT person_name
> FROM test_people p
> JOIN test_attributes a
> ON ((a.people_id = p.people_id) AND (a."attribute" = @firstAttr))
> JOIN test_attributes b
> ON ((b."people_id" = p."people_id") AND (b."attribute" = @secondAttr));
>
>
>
>

In response to

pgsql-sql by date

Next:From: Louis-David MitterrandDate: 2008-12-06 17:10:37
Subject: adding "order by" to a "group by" query
Previous:From: Bryce NesbittDate: 2008-12-05 20:52:41
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