Exclude records from query based on two joined tables results

I have an app that is used to inspecting residence rooms at a university. Staff goes through the rooms and inspects based on the list of inspection items and marks the condition as a pass or fail. every inspection has 25 detail lines that can be marked with a pass or fail

table 1 is the inspection table insp_no, Description, insp_type, room_no, status_code, edit_clerk

table 2 is the inspection line detail table insp_no, line_no, description, result

Table 1 is the inspection info and table 2 has the detail and results of the inspection. tables are joined on the insp_no.

The best I can do is isolate the lines with a fail.

SELECT  DISTINCT t1.insp_no, t1.description, t1.status_code, t1.room_no, t1.edit_clerk, 
t1.edit_date, t2.line_no, t2.description, t2.result
FROM t1
LEFT JOIN t2 on t2.insp_no = t1.insp_no
where t1.INSP_TYPE = 'room_CHECKLIST'
AND not(t2.result <>'fail');

But I want to do the reverse and return only the inspections out of table 1 that have no fail detail in table 2 and exclude any inspection # from table 1that has any of its t2 detail lines in a fail state.

I either get 1076 results which are every inspection because every inspection has detail lines that are pass or I get 309 results which are the number of individual detail lines that are failed.

I expect to get a result of 765 inspections with no FAIL lines. please help 🙂

Answer

Instead of joining, you could use a NOT EXISTS condition with a correlated subquery.

SELECT  t1.* 
FROM t1
WHERE 
    t1.INSP_TYPE = 'room_CHECKLIST'
    AND NOT EXISTS (
        SELECT 1 
        FROM t2 
        WHERE t2.insp_no = t1.insp_no
        AND t2.result ='fail'
    )

The NOT EXIST condition ensures that, for the current t1 record, there is no record in failed status in table t2.