Skip site navigation (1) Skip section navigation (2)

Re: Query question

From: "Medi Montaseri" <montaseri(at)gmail(dot)com>
To: "Stephan Szabo" <sszabo(at)megazone(dot)bigpanda(dot)com>
Cc: pgsql-sql(at)postgresql(dot)org
Subject: Re: Query question
Date: 2008-05-22 22:23:21
Message-ID: 8078a1730805221523l29a2b603l31310dfb4130ce69@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-sql
Thanks Stephan,

My real DDL include a forign key reference to T2.id and since I am ok with
NULL value then the "left outer join" indeed have solved the problem.

Thanks again
Medi

On Thu, May 22, 2008 at 2:50 PM, Stephan Szabo <sszabo(at)megazone(dot)bigpanda(dot)com>
wrote:

> On Thu, 22 May 2008, Medi Montaseri wrote:
>
> > Hi,
> > I can use some help with the following query please.
> >
> > Given a couple of tables I want to do a JOIN like operation. Except that
> one
> > of the columns might be null.
> >
> > create table T1 ( id serial, name varchar(20) );
> > create table T2 ( id serial, name varchar(20) );
> > create table T1_T2 ( id serial, t1_id integer not null , t2_id integer );
> >
> > Now I'd like to show a list of records from T1_T2 but reference T1 and T2
> > for the names instead of IDs. But T1_T2.t2_id might be null
> >
> > select T1_T2.id, T1.name , T2.name from T1, T2, T1_T2
> > where T1_T2.t1_id = T1.id and T1_T2.t2_id = T2.id
>
> What would you want it to do if T1_T2.t2_id has a value that isn't in T2?
> And should it do it for both T2 and T1? If using a NULL name is okay for
> both, you can look at outer joins, something like:
>
> select T1_T2.id, T1.name, T2.name from
>  T1_T2 left outer join T1 on (T1_T2.t1_id = T1.id)
>  left outer join T2 on (T1_T2.t2_id = T2.id)
>
> T1_T2 left outer join T1 on (T1_T2.t1_id = T1.id) will for example give
> you a row even if there's not a row in T1 with T1.id being the same as
> T1_T2.t1_id.  In that case, you'll get the fields from T1_T2 and NULLs for
> the fields from T1. The same between that table and T2 occurs with the
> second outer join.
>
>

In response to

pgsql-sql by date

Next:From: Ramasubramanian GDate: 2008-05-23 04:42:12
Subject: Re: Substract queries
Previous:From: Stephan SzaboDate: 2008-05-22 21:50:16
Subject: Re: Query question

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group