Re: returning rows from an implicit JOIN where results either exist in both tables OR only one table

On Thu, Dec 1, 2011 at 1:57 PM, David Johnston <polobo(at)yahoo(dot)com> wrote:
> -----Original Message-----
> From: pgsql-general-owner(at)postgresql(dot)org [mailto:pgsql-general-owner(at)postgresql(dot)org] On Behalf Of Lonni J Friedman
> Sent: Thursday, December 01, 2011 4:13 PM
> To: pgsql-general
> Subject: [GENERAL] returning rows from an implicit JOIN where results either exist in both tables OR only one table
>
>
> I'm stumbling over how to integrate those two tables with static data into the query.  The following query works fine as long as there's at least one row in the 'results' table that corresponds to each row in the pending table (however, it doesn't return anything for rows that only exist in 'pending' yet not yet in 'results'):
>
> -----------------------------------------
>
> Implicit JOINs are ALWAYS INNER JOINs
>
> Since you want to use an OUTER JOIN you must be explicit.
>
> I'm not going to try and figure out specifically what you need but from your quick description (all pending and results where available) you need to do something like
>
> "pending" LEFT OUTER JOIN "results" ON ("pending".active = "results".hostname AND "pending".submittracker = "results".submittracker AND "pending".cl = "results".cl)
>
> Then, for conditions dependent upon the "results" (or NULL-able) relation, you need to make sure you explicitly allow for the missing rows:
>
> ( "results".current_status IS NULL  OR ( your existing "results" conditions ) )
>
> http://www.postgresql.org/docs/9.0/interactive/queries-table-expressions.html#QUERIES-FROM

Thanks for your reply and input. I ended up putting together the
following query which does what I need:

SELECT pending.cl,
pending.id,
pending.buildid,
pending.build_type,
pending.active,
pending.submittracker,
pending.os,
pending.arch,
pending.osversion,
pending.branch,
pending.comment,
osversmap.osname,
buildlist.buildname,
results.logurl
FROM pending
JOIN osversmap
ON ( pending.os = osversmap.os
AND pending.osversion = osversmap.osversion )
JOIN buildlist
ON ( pending.buildid = buildlist.id )
LEFT OUTER JOIN results
ON ( pending.active = results.hostname
AND pending.submittracker = results.submittracker
AND pending.cl = results.cl
AND results.current_status != 'PASSED'
AND results.current_status NOT LIKE '%FAILED'
)
WHERE pending.owner = '$owner'
AND pending.completed = 'f'
ORDER BY pending.submittracker,
pending.branch,
pending.os,
pending.arch