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

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/