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

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).<br /><br />
SELECT<br/> DISTINCT<br /> person_name<br /> FROM test_people p<br /> JOIN test_attributes a<br /> ON ((a.people_id =
p.people_id)AND (a."attribute" = 'Dark Hair'))<br /> JOIN test_attributes b<br /> ON ((b."people_id" = p."people_id")
AND(b."attribute" = 'USA President'));<br /><br /> Here's the full test table<br /><br /> $ pg_dump --table=test_people
--table=test_attributes-p 5433 -i<br /> CREATE TABLE test_attributes (<br />     people_id integer,<br />     attribute
text<br/> );<br /> COPY test_attributes (people_id, attribute) FROM stdin;<br /> 10    The Devil<br /> 9   
Imaginary<br/> 8    Dark Hair<br /> 8    Dark Hair<br /> 8    USA President<br /> 10    Dark Hair<br /> \.<br /><br />
CREATETABLE test_people (<br />     people_id integer DEFAULT nextval('test_sequence'::regclass) NOT NULL,<br />    
person_nametext<br /> );<br /> COPY test_people (people_id, person_name) FROM stdin;<br /> 8    Obamba<br /> 9   
Santa<br/> 10    Satan<br /> \.<br /><br /><br /> Oliveiros Cristina wrote: <blockquote
cite="mid:00d401c9570e$f1fbcee0$ec5a3d0a@marktestcr.marktest.pt"type="cite"><style></style><div><font face="Arial"
size="2">Howdy,Bryce</font></div><div><font face="Arial" size="2">Could you please try this out and tell me if it gave
whatyou want.</font></div><div><font face="Arial" size="2">Best,</font></div><div><font face="Arial"
size="2">Oliveiros</font></div><div> </div><div><fontface="Arial" size="2">SELECT person_name<br /> FROM test_people
p<br/> JOIN test_attributes a<br /> ON ((a.people_id = p.people_id) AND (a."attribute" = @firstAttr))<br /> JOIN
test_attributesb<br /> ON ((b."people_id" = p."people_id") AND (b."attribute" =
@secondAttr));</font></div></blockquote><br/><br />