pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedwhere clause on a left outer join
2+ messages / 2 participants
[nested] [flat]
* where clause on a left outer join
@ 2004-09-22 09:08 Cris Carampa <cris119@operamail.com>
2004-09-27 00:21 ` Re: where clause on a left outer join Stephan Szabo <sszabo@megazone.bigpanda.com>
0 siblings, 1 reply; 2+ messages in thread
From: Cris Carampa @ 2004-09-22 09:08 UTC (permalink / raw)
To: pgsql-sql
Hello, let's suppose I have the following tables:
create table parent (
parent_id numeric primary key,
parent_data text
) ;
create table stuff (
stuff_id numeric primary key,
parent_id numeric references parent,
stuff_data text
) ;
And the following data:
crisdb=> select * from parent;
parent_id | parent_data
-----------+-------------
1 | aaa
2 | bbb
3 | ccc
(3 rows)
crisdb=> select * from stuff;
stuff_id | parent_id | staff_data
----------+-----------+------------
1 | 1 | xxx
2 | 1 | yyy
3 | 1 | zzz
(3 rows)
I wish to write a query that returns all rows from "parent" and, beside
of them, staff data with stuff_id=1 if available, otherwise null.
The following query:
select
par.parent_id,
stu.stuff_data
from
parent par left outer join stuff stu
on (
par.parent_id = stu.parent_id
)
where
stu.stuff_id = 1
;
Gives the following result:
parent_id | stuff_data
-----------+------------
1 | xxx
(1 row)
But this is not what I want.
The following query:
select
par.parent_id,
stu.stuff_data
from
parent par
left outer join
(
select
*
from
stuff
where
stuff_id = 1
) stu
on (
par.parent_id = stu.parent_id
)
;
Gives the following result:
parent_id | stuff_data
-----------+------------
1 | xxx
2 |
3 |
(3 rows)
Which is exacly what I want.
I'm wondering whether there is another way to get this result, without
using the online view.
Thank you. Kind regards,
--
Cris Carampa (spamto:cris119@operamail.com)
I got some John Coltrane on the stereo baby make it feel all right
I got some fine wine in the freezer mama I know what you like
I said a man works hard all day he can do what he wants to at night
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: where clause on a left outer join
2004-09-22 09:08 where clause on a left outer join Cris Carampa <cris119@operamail.com>
@ 2004-09-27 00:21 ` Stephan Szabo <sszabo@megazone.bigpanda.com>
0 siblings, 0 replies; 2+ messages in thread
From: Stephan Szabo @ 2004-09-27 00:21 UTC (permalink / raw)
To: Cris Carampa <cris119@operamail.com>; +Cc: pgsql-sql
On Wed, 22 Sep 2004, Cris Carampa wrote:
> Hello, let's suppose I have the following tables:
>
> create table parent (
> parent_id numeric primary key,
> parent_data text
> ) ;
>
> create table stuff (
> stuff_id numeric primary key,
> parent_id numeric references parent,
> stuff_data text
> ) ;
>
> And the following data:
>
> crisdb=> select * from parent;
> parent_id | parent_data
> -----------+-------------
> 1 | aaa
> 2 | bbb
> 3 | ccc
> (3 rows)
>
> crisdb=> select * from stuff;
> stuff_id | parent_id | staff_data
> ----------+-----------+------------
> 1 | 1 | xxx
> 2 | 1 | yyy
> 3 | 1 | zzz
> (3 rows)
>
> I wish to write a query that returns all rows from "parent" and, beside
> of them, staff data with stuff_id=1 if available, otherwise null.
>
> The following query:
>
> select
> par.parent_id,
> stu.stuff_data
> from
> parent par left outer join stuff stu
> on (
> par.parent_id = stu.parent_id
> )
> where
> stu.stuff_id = 1
> ;
I think
on (par.parent_id = stu.parent_id and stu.stuff_id=1)
will give the join you want.
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2004-09-27 00:21 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-09-22 09:08 where clause on a left outer join Cris Carampa <cris119@operamail.com>
2004-09-27 00:21 ` Stephan Szabo <sszabo@megazone.bigpanda.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox