-----Original Message-----
From: pgsql-general-ow...@postgresql.org
[mailto:pgsql-general-ow...@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 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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
