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

[Bryce Nesbitt]

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