Ask the Expert

Using LEFT OUTER JOIN query to get zero row counts in SQL

I'm having a problem with getting a count of rows in different categories using a multiple join statement.

select rs.stratum_id stratum,
       rs.treatment_group_id treatment_name,
       count(rs.patient_id) countz
  from rand_schedule rs, strata s, treatment_groups t
  where rs.stratum_id = s.stratum_id (+)
  and rs.treatment_group_id = t.treatment_group_id (+)
  and rs.status = 'U'
  group by rs.stratum_id, rs.treatment_group_id
  order by countz desc;

This query returns the three stratum/treatment combinations that have rows for them, but does not return the one combination that has no rows. I've tried using an NVL on the count but to no avail—it just seems to stop processing once it determines that there are no rows for a category. Is there any way to get it to report the zero row counts?

    Requires Free Membership to View

Yes, you simply need to write the LEFT OUTER JOIN query in the other direction (as it were). Your query, with the old-style outer joins using the "(+)" notation, ostensibly allows for rand_schedule rows to exist without matching rows in the strata or treatment_groups tables, which of course would never happen if foreign keys were being enforced.

SELECT rs.stratum_id            stratum
     , rs.treatment_group_id    treatment_name
     , COUNT(rs.patient_id)     countz
  FROM strata s
CROSS
  JOIN treatment_groups t
LEFT OUTER
  JOIN rand_schedule rs
    ON rs.stratum_id = s.stratum_id 
   AND rs.treatment_group_id = t.treatment_group_id 
   AND rs.status = 'U'
GROUP 
    BY rs.stratum_id
     , rs.treatment_group_id
ORDER 
    BY countz DESC;

The CROSS JOIN first obtains all combinations of strata and treatment_groups. Then, for each of these combinations, an attempt is made, using the LEFT OUTER JOIN, to find matching rows of the rand_schedule table. Where no such rows exist, of course, NULLs are returned for that combination. Thus, when the COUNT aggregate function attempts to count them, the NULLs aren't counted, and so the result is a count of zero.

This was first published in November 2008

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to: