Re: left outer join without rows from "left" table - Mailing list pgsql-general

From Sam Mason
Subject Re: left outer join without rows from "left" table
Date
Msg-id 20090216110958.GS32672@frubble.xen.chris-lamb.co.uk
Whole thread Raw
In response to left outer join without rows from "left" table  (Scara Maccai <m_lists@yahoo.it>)
List pgsql-general
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/

pgsql-general by date:

Previous
From: Ivan Sergio Borgonovo
Date:
Subject: idle DB and resources
Next
From: Sam Mason
Date:
Subject: Re: ask: select right(column) ???