Incorrect syntax SQL message

Tim Richardson 20 Reputation points

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]
SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
11,571 questions
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,454 questions
0 comments No comments
{count} votes

Accepted answer
  1. Dan Guzman 8,516 Reputation points

    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]

0 additional answers

Sort by: Most helpful