LEFT OUTER JOIN to a MIN/MAX row

Digg This!     StumbleUpon     Del.icio.us

RELATED CONTENT SQL How to check SQL query construction with the Mimer Validator Using the SQL GROUP BY clause for counting combinations How to use an SQL CASE expression How to sort an SQL UNION query with special ORDER BY sequence How to use string functions to make an SQL join An SQL solution for a customer order homework problem How to use SQL's POSITION function with substrings Using SQL date functions to get totals for last three days Using CASE in the SQL ORDER BY clause What's the difference between an SQL inner join and equijoin? Oracle development languages How to check SQL query construction with the Mimer Validator Understanding SQL string functions The top advice from Oracle experts in 2008 What's the difference between an SQL inner join and equijoin? Using LEFT OUTER JOIN query to get zero row counts in SQL How to return multiple values for THEN clause in an SQL CASE expression Can I concatenate row values in SQL? Should I try to avoid a LEFT OUTER JOIN in SQL? Tips for derived tables in SQL and using FULL OUTER JOINs How to write an SQL query for two foreign keys to the same table

RELATED RESOURCES 2020software.com, trial software downloads for accounting software, ERP software, CRM software and business software systems Search Bitpipe.com for the latest white papers and business webcasts Whatis.com, the online computer dictionary

The answer is to move the MIN/MAX condition out of the WHERE clause and into the ON clause.

The problem of specifying a condition in a LEFT OUTER JOIN has been discussed before. LEFT OUTER JOIN with ON condition or WHERE condition? (September 16, 2005) explains the difference.

Your query probably looked something like this:

SELECT t1.foo
     , t2.bar
  FROM table1 AS t1
LEFT OUTER
  JOIN table2 AS t2
    ON t2.table1_id = t1.id  
 WHERE t2.datefld = 
       ( SELECT MIN(datefld)
           FROM table2
          WHERE table1_id = t1.id )

The problem here is that the WHERE condition will surely filter out all unmatched rows from table1. If there is no matching row in table2, then the MIN will be NULL, and so the WHERE condition fails (nothing is equal to NULL).

SELECT t1.foo
     , t2.bar
  FROM table1 AS t1
LEFT OUTER
  JOIN table2 AS t2
    ON t2.table1_id = t1.id  
   AND t2.datefld = 
       ( SELECT MIN(datefld)
           FROM table2
          WHERE table1_id = t1.id )

Now, the MIN condition has been moved into the ON clause. In effect, the LEFT OUTER JOIN now says "get matching rows based on the keys and on the matching row being the MIN matching row." In other words, if there is no matching row in table2, the row from table1 is still returned.