NOT IN condition in SQL

sql ms-access

You can't use IN with more than one column but you can usually achieve the same effect using EXISTS:

SELECT *FROM tbl1WHERE NOT EXISTS(    SELECT *    FROM tbl2    WHERE tbl2.col1 = tbl1.col1        AND tbl2.col2 = tbl1.col2)

sql ms-access

General syntax:

where col not in (items)

Items can be

Addition 6 years later

All major platforms support tuples with NOT IN, for example

SELECT *FROM empoyeeWHERE (empID, @date) NOT IN   (SELECT empID, vacationDay   FROM empVacation  )

In this example we select everything from the employee table where the tuple of employee id and date are not in a table containing vacation days.

sql ms-access

Your question is a bit unclear. Is this what you need?

SELECT *FROM  MY_TABLE MTWHERE 'Smith' NOT IN (MT.FIRST_NAME)  AND 'Smith' NOT IN (MT.LAST_NAME)

This will show you all records where the search phrase ("Smith") is in neither the first_name nor the last_name column.

CodeHunter