Thread: left outer join without rows from "left" table

Hi all,

I want to get data from these tables:
TABID
integer id,
name varchar
example values:
1  'id1'
2  'id2'
[...]
TABA
integer id,
timestamp t,
integer a
example values:
1 '2009-02-13 00:00:00' 10
1 '2009-02-13 02:00:00' 19
TABB
integer id,
timestamp t,
integer b
example values:
1 '2009-02-13 00:00:00' 90
1 '2009-02-13 01:00:00'  109
in this form:
select * from TABID left outer join TABA on (id) left outer join TABB on TABB.id = TABID.id and TABA.t = TABB.t
So, basically, all the rows from table TABID joined with both table TABA and TABB. The problem is that some times TABB
doesn'thave the "t" values TABA has, and some other times TABA doesn't have the "t" values TABB has. 
So I would like an output like:
id, TABA.t, a, TABB.t, b
1 '2009-02-13 00:00:00' 10'2009-02-13 00:00:00' 90
1 NULL     NULL     '2009-02-13 01:00:00'  109
1 '2009-02-13 02:00:00'  19 NULL     NULL
How can I do it?


      Passa a Yahoo! Mail.

La webmail che ti offre GRATIS spazio illimitato,
antispam e messenger integrato.
http://it.mail.yahoo.com/              

On Mon, Feb 16, 2009 at 12:15:47AM -0800, Scara Maccai wrote:
> select * from TABID left outer join TABA on (id) left outer join TABB on TABB.id = TABID.id and TABA.t = TABB.t
> So, basically, all the rows from table TABID joined with both table
> TABA and TABB. The problem is that some times TABB doesn't have the
> "t" values TABA has, and some other times TABA doesn't have the "t"
> values TABB has.

I think you want to use a full outer join with slightly unusual
bracketing:

  SELECT t.id, COALESCE(a.t,b.t) AS t, a.a, b.b
  FROM tabid t LEFT JOIN (
      taba a FULL OUTER JOIN tabb b ON (a.id,a.t) = (b.id,b.t))
    ON t.id = COALESCE(a.id,b.id);

This will start by doing the inner most thing first, join taba to tabb
matching rows where the id and timestamp is the same, then go on to
match these to tabid.  The COALESCEs are needed because when either side
is missing their values will be NULL and hence we need to look at both.


--
  Sam  http://samason.me.uk/

Thank you: that's exactly what I needed.



> I think you want to use a full outer join with slightly unusual
> bracketing:
>
>   SELECT t.id, COALESCE(a.t,b.t) AS t, a.a, b.b
>   FROM tabid t LEFT JOIN (
>       taba a FULL OUTER JOIN tabb b ON (a.id,a.t) = (b.id,b.t))
>     ON t.id = COALESCE(a.id,b.id);
>
> This will start by doing the inner most thing first, join taba to tabb
> matching rows where the id and timestamp is the same, then go on to
> match these to tabid.  The COALESCEs are needed because when either side
> is missing their values will be NULL and hence we need to look at both.


      Passa a Yahoo! Mail.

La webmail che ti offre GRATIS spazio illimitato,
antispam e messenger integrato.
http://it.mail.yahoo.com/