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! -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
