CODEWITHSUNDEEP
sqlpostgresql
Jake Wagner
Jake Wagner

Reputation: 826

Find missing values from tables

I am trying to find a combination of values from three columns that exist in table A and Not in Table B. My code keeps blowing up on the where clause with Address comma. How can I fix this?

SELECT a.Address,a.Last_Name_First_Name,a.Actual_Sale_Date
FROM salesdataall a
WHERE a.Address, a.Last_Name_First_Name, a.Actual_Sale_Date
NOT IN (SELECT b.Address,b.Last_Name_First_Name,b.Actual_Sale_Date
                      FROM salesdataun b)


ERROR:  syntax error at or near ,
LINE 3:  WHERE a.Address,a.Last_Name_First_Name,a.A...
                        ^

Upvotes: 0

Views: 73

Answers (2)

chresse
chresse

Reputation: 5817

you can do it with a join:

SELECT a.Address,a.Last_Name_First_Name,a.Actual_Sale_Date
FROM salesdataall a
LEFT JOIN salesdataun b
ON a.Address = b.Address AND
   a.Last_Name_First_Name = b.Last_Name_First_Name AND
   a.Actual_Sale_Date = b.Actual_Sale_Date
WHERE a.Address IS NULL

Upvotes: 1

user330315
user330315

Reputation:

You forgot the parentheses:

SELECT a.Address,a.Last_Name_First_Name,a.Actual_Sale_Date
FROM salesdataall a
WHERE (a.Address, a.Last_Name_First_Name, a.Actual_Sale_Date)
      NOT IN (SELECT b.Address,b.Last_Name_First_Name,b.Actual_Sale_Date
              FROM salesdataun b)

This is one of the rare situations where it makes sense to put parentheses around a list of columns.

Upvotes: 2

Related Questions