SELECT all EXCEPT results in a subquery

Question

I have a query that joins several tables (3 or 4) and gets me results as expected.

SELECT DISTINCT test_title, stt_id FROM student_tests
LEFT JOIN student_test_answers ON sta_stt_num = stt_id 
JOIN tests ON stt_test_id = test_id
WHERE student_test_answer_id IS NULL 

I have another query that shows another set of data, it basically is this:

    SELECT test_id, COUNT(*) AS theCount FROM tests
    JOIN test_questions ON test_id= tq_test_id
    WHERE type= 'THE_TYPE'
    GROUP BY test_id
    HAVING theCount = 1

So basically I want to NOT include the results of this second query in the first one. the test_id would be the joining fields.

I have tried a WHERE NOT EXISTS ( -the above query -) but that returns no results which is not correct. I also tried 'NOT IN ( )'

Is there a better way of doing this?

Answer 1

Try something like this:

(SELECT test_id, COUNT(*) AS theCount FROM tests
JOIN test_questions ON test_id= tq_test_id
WHERE type= 'THE_TYPE'
GROUP BY test_id
HAVING theCount = 1) outer
LEFT JOIN (
      [OtherQuery]
) a ON outer.test_id = a.test_id 
WHERE a.test_id IS NULL