sharlene sharlene - 5 months ago 22
SQL Question

WHERE clause ignoring rows in LEFT OUTER JOIN

I have three tables:

clinic = (id,name,short_name,region,country,continent,area)
result_month =(id,year,result_quarter_id,month)
test_value_in_range_count =(clinic_id,result_month_id,patient_sub_set_id,test_value_range_id,number_of_values)


Sample data:

clinic

id region country continent area
3299 Kazakhstan Kazakhstan Eurasia Middle East/Asia


result_month

id year result_quarter_id month
200801 2008 2008Q1 1


test_value_in_range_count

no data for clinic id 3299 in clinic table. But the JOINS must return

I need to have all rows from result_month table with nulls from test_value_in_range_count. The problem is the
WHERE
clause. This stops from generating the rows because obviously data doesn't exist for certain rows from result_month into test_value_range_id.

Expected Result

clinic region country continent area ym vf
3299 Kazakhstan Kazakhstan Eurasia Middle East/Asia 201511 null


I have tried lot of various queries by splitting them up but with no luck. Any help or direction would be really appreciated.

SELECT
apc.id AS clinic,
apc.region,
apc.country,
apc.continent,
apc.area,
vrm.id AS ym,

SUM(CASE test_value_range_id WHEN '1124_1' THEN number_of_values ELSE 0 END) AS avf

FROM result_month vrm
LEFT JOIN test_value_in_range_count vt on vrm.id = vt.result_month_id
LEFT OUTER JOIN clinic apc on vt.clinic_id = apc.id

WHERE (vt.test_value_range_id IN ('1124_1', '1124_2', '1124_3', '1124_4', '1124_5')) AND (vt.patient_sub_set_id = 'ALL')
GROUP BY apc.id,
apc.region,
apc.country,
apc.continent,
apc.area,
vrm.id

;

Answer

Remove the condition from where clause and add it to the join: SELECT apc.id AS clinic, apc.region, apc.country, apc.continent, apc.area, vrm.id AS ym,

    SUM(CASE test_value_range_id WHEN '1124_1' THEN number_of_values ELSE 0 END) AS avf

FROM result_month vrm
    LEFT JOIN  test_value_in_range_count vt on  vrm.id = vt.result_month_id and (vt.test_value_range_id IN ('1124_1', '1124_2', '1124_3', '1124_4', '1124_5')) AND (vt.patient_sub_set_id = 'ALL')
    LEFT OUTER JOIN clinic apc on vt.clinic_id = apc.id

GROUP BY apc.id, 
    apc.region, 
    apc.country, 
    apc.continent,
    apc.area, 
    vrm.id   

    ; 

Else the where clause makes a join from your left join

Comments