Re: Should be easy enough to get this result (or is it possible?)...
От | Sean Chittenden |
---|---|
Тема | Re: Should be easy enough to get this result (or is it possible?)... |
Дата | |
Msg-id | 20020515235116.N54403@ninja1.internal обсуждение исходный текст |
Ответ на | Re: Should be easy enough to get this result (or is it (Stephan Szabo <sszabo@megazone23.bigpanda.com>) |
Список | pgsql-general |
> > 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. Cha-ching! Yeah, you successfully clubbed me w/ the clue-bat: left join on a sub-select gave me exactly what I wanted: thank you. -sc -- Sean Chittenden
В списке pgsql-general по дате отправления: