Обсуждение: returning rows from an implicit JOIN where results either exist in both tables OR only one table

Greetings,
I've got a PostgreSQL-9.0.x database that manages an automated testing
environment.  There are a bunch of tables that contain assorted static
data (OS versions, test names, etc) named 'buildlist' & 'osversmap'.
However, there are also two tables which contain data which changes
often.  The first is a 'pending' table which is effectively a test
queue where pending tests are self-selected by the test systems, and
then deleted when the test run has completed.  The second is a
'results' table which contains the test results as they are produced
(in progress and completed).

The records in the pending table have a one to many relationship with
the records in the results table (each row in pending can have 0 or
more rows in results).  For example, if no test systems have
self-assigned a pending row, then there will be zero associated rows
in results, and then once a pending row is assigned, the number of
rows in results will increase for each pending row.  An added catch is
that I always want only the newest results table row associated with
each pending table row.  What I need to do is query the 'pending'
table for pending tests, and then also get a 'logurl' from the results
table that corresponds to each pending table row.

All of this is rather similar to this problem, except that I have the
added burden of the two additional tables with the static data
(buildlist & osversmap):

http://stackoverflow.com/questions/3343857/php-sql-using-only-one-query-select-rows-from-two-tables-if-data-is-in-both-ta

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'):

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 ,osversmap ,buildlist ,results
WHERE
pending.buildid=buildlist.id
AND pending.os=osversmap.os
AND pending.osversion=osversmap.osversion
AND pending.owner='$owner'
AND pending.completed='f'
AND results.hostname=pending.active
AND results.submittracker=pending.submittracker
AND pending.cl=results.cl
AND results.current_status!='PASSED'
AND results.current_status NOT LIKE '%FAILED'
ORDER BY pending.submittracker,pending.branch,pending.os,pending.arch


thanks in advance!

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

On Thu, Dec 1, 2011 at 1:57 PM, David Johnston <polobo@yahoo.com> wrote:
> -----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
resultswhere available) 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

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