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

-----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