Re: Best way to "and" from a one-to-many joined table? - Mailing list pgsql-sql

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