On Thu, Dec 1, 2011 at 1:57 PM, David Johnston <[email protected]> wrote:
> -----Original Message-----
> From: [email protected]
> [mailto:[email protected]] 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
--
Sent via pgsql-general mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
