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.
|
