How to Resolve Syntax Error in SQL Query for Retrieving Missing Rows

Data and Databases
1

I’m attempting to filter out missing entries from two data sets by comparing two SQL queries that function properly on their own. However, when I combine them, I encounter a syntax error. Here’s the SQL code I’m using:

SELECT Name_ID FROM [Data_Set1] 
 WHERE Pool_Type       = 'Active' 
   AND Evaluation_Date  = '20151231' 
   AND Key_Value        = '34' 
   AND Currency         = 'EUR' 
   AND Business_Line    = 'KLV'
 AND Name_ID NOT IN (
      SELECT DISTINCT VD.Name_ID   
        FROM [Data_Set2] VD 
  INNER JOIN [Data_Set1] SD 
          ON VD.Pool_Type = SD.Pool_Type   
         AND VD.Name_ID = SD.Name_ID   
         AND VD.Evaluation_Date = SD.Evaluation_Date 
         AND VD.Key_Value = SD.Key_Value   
         AND VD.Currency = SD.Currency   
       WHERE SD.Pool_Type       = 'Active' 
         AND SD.Evaluation_Date   = '20151231' 
         AND SD.Key_Value         = '34' 
         AND SD.Currency          ='EUR' 
         AND SD.Business_Line     ='KLV'
    )

I’m experiencing a syntax error close to the WHERE clause. What changes do I need to make in the structure of this query to correctly return the values that are not included in the second part of the query? I should mention that I’m executing this query through a pass-through query in MS Access to communicate with a Microsoft SQL Server.

Thanks in advance for your assistance!

2

Check your parentheses, they might not be correctly balanced around the subquery. Also, ensure there are no typos, like extra or missing commas in your WHERE conditions. SQL syntax can be picky about these things, especially when nesting queries.

3

Ensure that the tables specified in the FROM clause are correctly referenced when defining aliases, as incorrect aliases might cause SQL Server to fail at parsing the query. Additionally, review the usage of the DISTINCT keyword; depending on your logic, it might not be necessary. Occasionally, simplifying the subquery or breaking it into smaller parts for testing can reveal overlooked mistakes or logical errors. Testing each component separately can also provide clearer error messages, making debugging easier.

4

Hey Liam27! Could it be that you’re facing issues with alias references? Sometimes misaligning them in your JOIN might cause the trouble. How does it work when you simplify conditions temporarily—like maybe removing and then individually readding WHERE criteria? Curious if that changes anything :star2:

5

Double-check your table names and schema references if you’re running this in a different database context within Access. Sometimes MS Access interpretations can mess up SQL syntax. Try running each SELECT separately and see how they respond differently; it might give clues to hidden issues.