Postgres Pro
Downloads
Home   >   mailing lists

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

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

From
Sean Chittenden
Date:
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
Attachment

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

From
Tom Lane
Date:
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

From
Stephan Szabo
Date:
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?)...

From
Sean Chittenden
Date:
> > 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