From: | Steve Midgley <science(at)misuse(dot)org> |
---|---|
To: | pgsql-sql(at)postgresql(dot)org |
Subject: | Re: Best way to "and" from a one-to-many joined table? |
Date: | 2008-12-08 02:27:36 |
Message-ID: | 20081208022803.94F496500D5@mail.postgresql.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
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.
From | Date | Subject | |
---|---|---|---|
Next Message | John Lister | 2008-12-08 08:51:29 | Re: Best way to "and" from a one-to-many joined table? |
Previous Message | Bruce Momjian | 2008-12-06 22:31:23 | Re: Public synonyms |