WHERE clause ignoring rows in LEFT OUTER JOIN

Question

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   

        ;

Jens · Accepted Answer · 2020-05-13T04:28:45.097

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

WHERE clause ignoring rows in LEFT OUTER JOIN

1 Answers1