Joins Specific Querying

Question

I'm using SQL Server 2017 and I have a question on how to return specific values (I'm not sure 100% if I should be using joins or subqueries.) I tried to put an example below of what I'm trying to do and hopefully someone can help me out with this.

I have two tables below:

Table 1

Record	Topic
100	History
101	Science
102	Art
103	Music

Table 2

Record	Course
100	Intro
100	Intermediate
101	Intermediate
101	Advanced
102	Intro
102	Intermediate
103	Intermediate
103	Advanced

I want to join the two tables together, but only bring back the columns 'Record' and 'Topic' ONLY where the Record DOES NOT have an 'Intro' course attached to it.

So I would want to bring back the following results (since both Courses do not have an Intro class):

Record	Topic
101	Science
103	Music

So far I've had no luck and am bringing back more records than I should so any help or guidance would be appreciated. Thank you in advance.

Answer 1

You could use exists logic here:

SELECT Record, Topic
FROM Table1 t1
WHERE NOT EXISTS (
    SELECT 1
    FROM Table2 t2
    WHRE t2.Record = t1.Record AND
         t2.Course = 'Intro'
);