CODEWITHSUNDEEP
phpmysqlwhere-in
Mikey
Mikey

Reputation: 6766

Is there a way to retrieve only the values in the IN (or NOT IN) clause that did not match a record in a table?

This might be a weird question. Currently, I have a table with a bunch of data in it. Using PHP, I am dynamically creating the IN clause.

SELECT * FROM table
WHERE field IN ('str1', 'str2', [...])

Let's say no records matched with 'str1' or 'str2', is there a way to retrieve 'str1' and 'str2'?

Upvotes: 1

Views: 84

Answers (3)

user330315
user330315

Reputation: 

SELECT dmy.id
FROM (
   SELECT 'str1' as id
   UNION ALL
   SELECT 'str2' 
   UNION ALL
   .....
) dmy
LEFT JOIN some_table ON some_table.field = dmy.id
WHERE some_table.field IS NULL

On special request, here is an explanation:

The derived table (the inner select) just creates a "dummy" table that contains all the desired values. Then this dummy table is outer joined to the real table. Anything that has no value (NULL) in the outer joined table (some_table) does not exist in the list of values from the dummy "table".

To see the effect of the derived table just run it stand alone without the join:

SELECT dmy.id
FROM (
   SELECT 'str1' as id
   UNION ALL
   SELECT 'str2' 
   UNION ALL
   .....
) dmy

Upvotes: 1

m0ntassar
m0ntassar

Reputation: 310

Or simply 

SELECT field from table WHERE field IN ( .. ) GROUP BY field;

this wil return a 1 an array containing on the list of values that have at least one match. Then it's easy to figure out by comparing the initial list with the obtained array to figure out values with no match

Upvotes: 0

liquorvicar
liquorvicar

Reputation: 6106

As you're building up the query dynamically, keep a record of the str values in an array. Then when you run your query and you check the number of results, if there are no results you can do whatever you need to do with the array of strings (I'm guessing you want to log this somewhere).

Upvotes: 0

Related Questions