Why is SQL query only returning records for a single item in 'WHERE' 'IN' clause?

Question

Why does this query:

SELECT
        dr.facility_id,
        ds.sys_sample_code,
        ds.sys_loc_code,
        ds.matrix_code,
        ds.sample_type_code,
        ds.start_depth,
        ds.end_depth,
        ds.sample_date,
        ds.task_code,
        dt.prep_date,
        dt.analysis_date,
        dt.lab_name_code,
        dt.lab_sdg,
        dt.lab_sample_id,
        dt.analytic_method,
        dt.prep_method,
        dr.cas_rn,
        dr.custom_field_1 as chemical_name, 
        dr.detect_flag,
        dr.result_numeric as result_value_rdl,
        dr.result_numeric as result_value_mdl,
        dr.result_numeric as result_text_rdl,
        dr.result_numeric as result_text_mdl,
        dr.validator_qualifiers as final_qualifier,
        dr.lab_qualifiers,
        dr.result_unit,
        dr.method_detection_limit,
        dr.quantitation_limit,
        dr.reporting_detection_limit,
        dr.detection_limit_unit,
        dr.reportable_result,
        dr.result_type_code,
        dt.percent_moisture,
        dt.fraction, dt.basis,
        dt.dilution_factor,
        dr.custom_field_2 as validation_date,
        dr.dqm_remark as validation_comment,
        dr.custom_field_3 as validator_name,
        dr.validated_yn, 
        NULL as result_comment,
        dr.approval_code,
        dc.x_coord, dc.y_coord,
        'Groundwater' AS matrix_name
    FROM [dbo].dt_sample ds
    INNER JOIN [dbo].dt_test dt ON dt.sample_id = ds.sample_id
    INNER JOIN [dbo].dt_result dr ON dr.test_id = dt.test_id
    LEFT JOIN [dbo].dt_coordinate dc ON ds.sys_loc_code = dc.sys_loc_code
    WHERE dt.lab_sdg IN ('X3A0217', 'X3B0341', 'X3C0002', 'X3D0121', 'X3E0289', 'X3E0336')
    AND dt.facility_id = 928061
) subq;

return only records for 'X3B0341' when:

SELECT dt.lab_sdg, COUNT(*)
FROM [dbo].dt_test dt
WHERE dt.lab_sdg IN ('X3A0217', 'X3B0341', 'X3C0002', 'X3D0121', 'X3E0289', 'X3E0336')
--AND dt.facility_id = 928061
GROUP BY dt.lab_sdg;

Gives this output (obviously data availability is not an issue):

X3B0341 408
X3C0002 239
X3D0121 438
X3E0289 673
X3E0336 303

I have tired changing up the WHERE to select fewer values like this WHERE dt.lab_sdg = 'X3D0121' OR dt.lab_sdg = 'X3E0336' to no avail. I can get the desired results with a giant script with multilple unions for each dt.lab_sdg but I would like to avoid that. Any and all help is greatly appreciated.

Answer 1

I think you should also check those INNER JOIN logic. It might be possible that for data "X3B0341", there is no data in those tables. I would have commented out the tables one by one used in the INNER JOIN, to find out that.

SELECT *
    FROM [dbo].dt_sample ds
    INNER JOIN [dbo].dt_test dt ON dt.sample_id = ds.sample_id
    --INNER JOIN [dbo].dt_result dr ON dr.test_id = dt.test_id
    --LEFT JOIN [dbo].dt_coordinate dc ON ds.sys_loc_code = dc.sys_loc_code
    WHERE dt.lab_sdg IN ('X3A0217', 'X3B0341', 'X3C0002', 'X3D0121', 'X3E0289', 'X3E0336')
    AND dt.facility_id = 928061;

Also check without the where clause, are you getting data other than "X3B0341".

SELECT *
    FROM [dbo].dt_sample ds
    INNER JOIN [dbo].dt_test dt ON dt.sample_id = ds.sample_id
    INNER JOIN [dbo].dt_result dr ON dr.test_id = dt.test_id
    LEFT JOIN [dbo].dt_coordinate dc ON ds.sys_loc_code = dc.sys_loc_code
    WHERE 
    --dt.lab_sdg IN ('X3A0217', 'X3B0341', 'X3C0002', 'X3D0121', 'X3E0289', 'X3E0336')
    dt.facility_id = 928061;

Answer 2

Change the order of tables and left join the rest. The other tables must not have related values that match to the wanted list: e.g:

SELECT
      dt.lab_sdg
    , dr.facility_id
    , ds.sys_sample_code
    , ds.sys_loc_code
    -- more as needed
FROM  [dbo].dt_test dt
LEFT JOIN [dbo].dt_sample ds ON dt.sample_id = ds.sample_id
LEFT JOIN [dbo].dt_result dr ON dr.test_id = dt.test_id
LEFT JOIN [dbo].dt_coordinate dc ON ds.sys_loc_code = dc.sys_loc_code
WHERE dt.lab_sdg IN ('X3A0217', 'X3B0341', 'X3C0002', 'X3D0121', 'X3E0289', 'X3E0336')
    AND dt.facility_id = 928061