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

At 11:20 AM 12/6/2008, pgsql-sql-owner(at)postgresql(dot)org wrote:
>Message-ID: <00d401c9570e$f1fbcee0$ec5a3d0a(at)marktestcr(dot)marktest(dot)pt>
>From: "Oliveiros Cristina" <oliveiros(dot)cristina(at)marktest(dot)pt>
>To: "Bryce Nesbitt" <bryce2(at)obviously(dot)com>,
> "sql pgsql" <pgsql-sql(at)postgresql(dot)org>
>References: <4939791B(dot)5090604(at)obviously(dot)com>
>Subject: Re: Best way to "and" from a one-to-many joined table?
>Date: Fri, 5 Dec 2008 19:23:25 -0000
>
>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));

Hi,

I saw a few people post answers to this question and it raised another
related question for me.

What are the differences between the above query and this one. Are they
semantically/functionally identical but might differ in performance? Or
would they be optimized down to an identical query? Or am I misreading
them and they are actually different?

SELECT person_name
FROM test_people p
JOIN test_attributes a
ON ((a.people_id = p.people_id)
JOIN test_attributes b
ON ((b."people_id" = p."people_id")
WHERE
(a."attribute" = @firstAttr))
AND (b."attribute" = @secondAttr));

Also, any suggestions about how to figure out this on my own without
bugging the list in the future would be great. Thanks for any insight!

Steve

p.s. I posting in the same thread, but if you think I should have
started a new thread let me know for the future.