Обсуждение: Should be easy enough to get this result (or is it possible?)...

Поиск
Список
Период
Сортировка

Should be easy enough to get this result (or is it possible?)...

От
Sean Chittenden
Дата:
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
Вложения

Re: Should be easy enough to get this result (or is it possible?)...

От
Tom Lane
Дата:
Sean Chittenden <sean@chittenden.org> writes:
> The upshot of things being that I want the 2nd query below (f.foo =3D
> 'b') to return foo_id and foo.  Am I missing something?

I'm not real clear on what you're after, but the query results look
correct.  The LEFT JOIN result (without any WHERE restriction) is

test=# SELECT f.foo_id, f.foo, b.bar_id, b.bar
test-# FROM foo AS f LEFT JOIN bar AS b on (f.foo_id = b.foo_id);
 foo_id | foo | bar_id | bar
--------+-----+--------+-----
      1 | a   |      2 | y
      1 | a   |      1 | x
      1 | a   |      3 | z
      2 | b   |      4 | x
      2 | b   |      5 | z
      3 | c   |        |
(6 rows)

and so the restricted outputs with the WHERE clauses look right.
What were you trying to do exactly?

            regards, tom lane

Re: Should be easy enough to get this result (or is it

От
Stephan Szabo
Дата:
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.

Re: Should be easy enough to get this result (or is it possible?)...

От
Sean Chittenden
Дата:
> > 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