[thelist] trouble w/ JOINs in an Oracle query

[r937]

> The above query works great except that it only returns rows 
> when both columns exist, as it rightly should since it's an 
> INNER JOIN.

then try LEFT OUTER JOIN    ;o)

also, use table aliases to make your query a bit easier to read

always start your FROM clause with the most restricted table, in this case 
you have a WHERE condition on the users table (departmentkey) so this is 
what's driving the query

SELECT TO_CHAR(l.started,'yyyy-mm') startmonth
     , SUM(l.totaltime) sumoftotaltime
  FROM corr.users u
LEFT OUTER
  JOIN corr.workorderlabor l
    ON l.performedbykey = u.userkey
   AND l.started >= '2009-01-01'
   AND l.started  < '2009-10-01'
LEFT OUTER
  JOIN corr.workordersquawks s
    ON s.squawkkey = l.squawkkey
LEFT OUTER
  JOIN corr.workorderitems i
    ON i.itemkey = s.itemkey
   AND i.costworkordercustomer LIKE '%$customername%'
LEFT OUTER
  JOIN corr.workordercustomers c
    ON c.workorderkey = i.workorderkey
 WHERE u.departmentkey = $deptkey
GROUP
    BY TO_CHAR(l.started, 'yyyy-mm')
ORDER
    BY TO_CHAR(l.started, 'yyyy-mm')

the last join, to workordercustomers, is not needed and can safely be 
omitted

rudy