From: | Stephan Szabo <sszabo(at)megazone23(dot)bigpanda(dot)com> |
---|---|
To: | Sean Chittenden <sean(at)chittenden(dot)org> |
Cc: | <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Should be easy enough to get this result (or is it |
Date: | 2002-05-16 05:25:12 |
Message-ID: | 20020515222016.T81293-100000@megazone23.bigpanda.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On Wed, 15 May 2002, Sean Chittenden wrote:
> I think the following code explains my problem more elegantly than I
> could ever hope to try and explain in a reasonable amount of words.
> The upshot of things being that I want the 2nd query below (f.foo =
> 'b') to return foo_id and foo. Am I missing something? My head
> stands poised to get clobbered with the clue-bat. Here's the test
> case:
>
> CREATE TABLE foo ( foo_id SERIAL NOT NULL, foo VARCHAR(32) NOT NULL );
> CREATE TABLE bar ( bar_id SERIAL NOT NULL, foo_id INT NOT NULL, bar VARCHAR(32) NOT NULL);
> INSERT INTO foo (foo) VALUES ('a');
> INSERT INTO foo (foo) VALUES ('b');
> INSERT INTO foo (foo) VALUES ('c');
> INSERT INTO bar (foo_id, bar) VALUES ('1','x');
> INSERT INTO bar (foo_id, bar) VALUES ('1','y');
> INSERT INTO bar (foo_id, bar) VALUES ('1','z');
> INSERT INTO bar (foo_id, bar) VALUES ('2','x');
> INSERT INTO bar (foo_id, bar) VALUES ('2','z');
>
> SELECT f.foo_id, f.foo, b.bar_id, b.bar
> FROM foo AS f LEFT JOIN bar AS b on (f.foo_id = b.foo_id)
> WHERE (b.bar ISNULL OR b.bar = 'y') AND f.foo = 'b';
> foo_id | foo | bar_id | bar
> --------+-----+--------+-----
> (0 rows)
I think you want something like (not completely tested):
SELECT f.foo_id, f.foo, b.bar_id, b.bar
from foo as f left join
(select * from bar b where b.bar='y' or b.bar is null) as b
on (f.foo_id=b.foo_id) where f.foo='b';
You want to limit the bar rows you're left joining to, not
the rows from the output of the join I think.
From | Date | Subject | |
---|---|---|---|
Next Message | Doug Fields | 2002-05-16 05:51:55 | Re: Force a merge join? |
Previous Message | Lincoln Yeoh | 2002-05-16 05:01:43 | Re: Force a merge join? |