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

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));
>
>
>
>