[thelist] trouble w/ JOINs in an Oracle query
r937
rudy at r937.com
Thu Oct 1 07:12:03 CDT 2009
> 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
More information about the thelist
mailing list