CODEWITHSUNDEEP
mysql
bansi
bansi

Reputation: 57002

Select id from array which is not in table

I have a PHP coma separated string of ids like 1,2,3. I have a MySQL table which has id column

Table task_comments:

id
--
1
2

I want to get all the ids in the list which are not in the table. Here i would like to get the 3 as result.

Currently I am building a query like the following in PHP and it is working.

SELECT id FROM (
SELECT 1 id FROM DUAL
UNION ALL
SELECT 2 id FROM DUAL
UNION ALL
SELECT 3 id FROM DUAL
) a WHERE id NOT IN (SELECT id FROM task_comments);

I don't think this is a good way to do this. I want to know if there is a better method to do this, because if the list is big the union list will grow.

Thanks

PS: I can post the PHP code used to make the query also if needed.
PPS: I would like to know if there is better MySQL Query.

Upvotes: 3

Views: 8771

Answers (4)

sorencito
sorencito

Reputation: 2625

What you do is already the way to do it. There is no other way to create sets to reason over than the (pretty ugly) union construct. You can leave of the "from dual"s and replace the union alls with plain unions to make it shorter - although with a very large list union all might be the more performant solution as it does not sort for duplicate deletion.

SELECT id FROM (
SELECT 1 id
UNION
SELECT 2 id
UNION 
SELECT 3 id
) a WHERE id NOT IN (SELECT id FROM tasklist);

You might also want to have a look at temporary tables. That way you could create the set you need in a more natural way without hitting the limits of the large SQL involving unions.

CREATE TEMPORARY TABLE temp_table (id int);

INSERT INTO temp_table VALUES((1),(2),(3)); -- or just repeat for as many values as you might have from your app (batch insert?)

SELECT id FROM temp_table
WHERE id NOT IN (SELECT id FROM tasklist);

See more on temporary tables here.

Upvotes: 4

Alma Do
Alma Do

Reputation: 37365

You can do it like that: select your ids:

SELECT id FROM task_comments WHERE id IN (1,2,3)

(here (1,2,3) is built from your array data - for example, via implode() function)

Then, in a cycle, fetch your ids into an array and then use array_diff() to find absent values.

Upvotes: 2

Sahil Mittal
Sahil Mittal

Reputation: 20753

May be you should first save all the distinct id's from the table that are present in your string of id's -

SELECT DISTINCT id FROM task_comments WHERE id IN (1,2,3..)

and then compare the two.

Upvotes: 0

nyzm
nyzm

Reputation: 2825

Your string separated values in PHP: 

$my_ids = "1,2,3";

SQL query in PHP:

$query = "SELECT id FROM task_comments WHERE id IN ($my_ids)";

This will return the id values from database which is 1 or 2 or 3. Then you can simply compare it.

Upvotes: 4

Related Questions