-----Original Message-----
From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.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
aslong as there's at least one row in the 'results' table that corresponds to each row in the pending table (however,
itdoesn'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
whereavailable) you need to do something like
"pending" LEFT OUTER JOIN "results" ON ("pending".active = "results".hostname AND "pending".submittracker =
"results".submittrackerAND "pending".cl = "results".cl)
Then, for conditions dependent upon the "results" (or NULL-able) relation, you need to make sure you explicitly allow
forthe 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