pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  

help / color / mirror / Atom feed

From: Stephan Szabo <sszabo@megazone.bigpanda.com>
To: Cris Carampa <cris119@operamail.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: where clause on a left outer join
Date: Sun, 26 Sep 2004 17:21:13 -0700 (PDT)
Message-ID: <20040926171842.A46859@megazone.bigpanda.com> (raw)
In-Reply-To: <cirfem$75c$1@floppy.pyrenet.fr>
References: <cirfem$75c$1@floppy.pyrenet.fr>


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.

view thread (2+ messages)

Message-ID: <20040926171842.A46859@megazone.bigpanda.com>
Permalink:  ../20040926171842.A46859@megazone.bigpanda.com/
Also on:    postgresql.org/message-id/20040926171842.A46859@megazone.bigpanda.com

copy link · copy postgr.es

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-sql@postgresql.org
  Cc: sszabo@megazone.bigpanda.com, cris119@operamail.com
  Subject: Re: where clause on a left outer join
  In-Reply-To: <20040926171842.A46859@megazone.bigpanda.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

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