Should be easy enough to get this result (or is it possible?)...
От | Sean Chittenden |
---|---|
Тема | Should be easy enough to get this result (or is it possible?)... |
Дата | |
Msg-id | 20020515151852.H54403@ninja1.internal обсуждение исходный текст |
Ответы |
Re: Should be easy enough to get this result (or is it possible?)...
Re: Should be easy enough to get this result (or is it |
Список | 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 = 'a'; foo_id | foo | bar_id | bar --------+-----+--------+----- 1 | a | 2 | y (1 row) 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) 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 = 'c'; foo_id | foo | bar_id | bar --------+-----+--------+----- 3 | c | | (1 row) Any help/ideas/suggestions? -sc -- Sean Chittenden
Вложения
В списке pgsql-general по дате отправления: