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@obviously.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));
