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!