Incorrect syntax SQL message

Question

Running this code returns the error Msg 102, Level 15, State 1, Line 23

Incorrect syntax near 'NewNumbersTest'.

What am I doing wrong?

I'm pretty new to SQL, as you can guess, and know it is simple, but I've followed online examples and it still doesn't work. I'm basically wanting to pull back numbers from the original [Lottery Numbers analysis] table that do not exist in the new [NewNumbersTest] table.

 Select [Lottery Numbers analysis].DrawNumber
  From [Lottery Numbers analysis] a
join [Lottery Numbers analysis] on [NewNumbersTest].[DrawNumber] = a.DrawNumber
 where a.DrawNumber not in [NewNumbersTest].[DrawNumber]

Answer 1

A NOT IN clause expects either a subquery or a comma-separated list of values. I think you want a subquery here and remove the JOIN clause too:

SELECT a.DrawNumber
FROM [Lottery Numbers analysis] a
WHERE a.DrawNumber NOT IN (
    SELECT [NewNumbersTest].DrawNumber 
    FROM [NewNumbersTest]
);