To choose values not in the table


Warning: count(): Parameter must be an array or an object that implements Countable in /home/styllloz/public_html/qa-theme/donut-theme/qa-donut-layer.php on line 274
0 like 0 dislike
5 views
Hello.

In the table column item_id consist of numbers 1, 2, 3, 4. I have a list of the numbers 2, 3, 5. Need a query which will return only 5, that is, the value of which is not in the table but is in the query.

I do not understand how to do this query?
It is clear when the data in the two tables, but when the list in the query, the query becomes not obvious.
by | 5 views

6 Answers

0 like 0 dislike
uh...
\r
SELECT * FROM table WHERE item_id IN (2, 3, 5) AND item_id NOT IN (SELECT item_id FROM table); 
by
0 like 0 dislike
And if you create a temporary table?
by
0 like 0 dislike
To collect from 2,3,5 flop table. To do SELECT a.id my_tmp_table as a LEFT JOIN my_values as b on a.id=b.id WHERE isnull(b.id) this option would be the fastest and will work for a sane, even for a large number of items.
by
0 like 0 dislike
And about the EXCEPT operator does anyone remember?
\r
SELECT 2 AS item_id
UNION
SELECT 3 AS item_id
UNION
SELECT 5 AS item_id
EXCEPT
SELECT item_id FROM table
by
0 like 0 dislike
The first thing that came to mind (MySQL):
SELECT tmp.id
FROM (
SELECT 2 AS id
UNION
SELECT 3 AS id
UNION
SELECT 5 AS id
) tmp
T LEFT JOIN table c ON ( t.item_id = tmp.id )
WHERE t.item_id IS NULL
by
0 like 0 dislike
Still it is possible so:
\r
SET @var = '{2}{3}{5}'; SELECT SUBSTRING ( @var := REPLACE ( ( SELECT @var := REPLACE(@var, concat('{', `item_id`, '}'), ") AS `ids` FROM `table` ORDER BY `ids` DESC LIMIT 0,1 ), '}{', ',' ), 2, length(@var) - 2 ) 

String '{2}{3}{5}', script form.
For one record, the query will return '5' for a few, like '5,6,7'.
In MySQL I have running.
by

Related questions

0 like 0 dislike
3 answers
0 like 0 dislike
2 answers
0 like 0 dislike
2 answers
0 like 0 dislike
2 answers
110,608 questions
257,186 answers
0 comments
26,164 users