2

I have a table with an id and a version number (and several other fields) and I want to do a lookup for all rows that do not match the id and version I know.

I have tried something like this, but it is not quite what I wanted.

SELECT * FROM table WHERE id NOT IN (1,2,3) AND version NOT in (4,5,6);

Above query appears to work like this:

SELECT * FROM table WHERE id != 1 AND version != 4;
SELECT * FROM table WHERE id != 1 AND version != 5;
SELECT * FROM table WHERE id != 1 AND version != 6;
SELECT * FROM table WHERE id != 2 AND version != 4;
SELECT * FROM table WHERE id != 2 AND version != 5;
SELECT * FROM table WHERE id != 2 AND version != 6;
SELECT * FROM table WHERE id != 3 AND version != 4;
SELECT * FROM table WHERE id != 3 AND version != 5;
SELECT * FROM table WHERE id != 3 AND version != 6;

What I actually want, is to use the same array key for both lists, like this:

SELECT * FROM table WHERE id != 1 AND version != 4;
SELECT * FROM table WHERE id != 2 AND version != 5;
SELECT * FROM table WHERE id != 3 AND version != 6;

So this dataset returns only the second row.

id | version
------------
 1 | 4
 2 | 7
 3 | 6

I tried to search SQL documentation for something like a for loop, but can't really find out if it exists and how it is supposed to work.

Neograph734
  • 1,714
  • 2
  • 18
  • 39
  • Some more reading after this syntax led me here: http://stackoverflow.com/questions/13426203/mysql-how-to-bulk-select-rows-with-multiple-pairs-in-where-clause. There are some good suggestions on optimization and larger datasets there. Hope it helps others. – Neograph734 Feb 17 '16 at 23:43

1 Answers1

4

This should work:

SELECT * FROM table WHERE (id, version) not in ((1,4), (2,5))

Update:

Here's a fiddle: http://sqlfiddle.com/#!9/b3284b/1

Andrés Esguerra
  • 849
  • 5
  • 15
  • Thanks! To match my example I made the query like this: `SELECT * FROM table WHERE (id, version) not in ((1, 4), (2, 5), (3, 6))` and it returned row 2 as expected (2,7). – Neograph734 Feb 17 '16 at 19:26
  • I wish SQL Server could do this without going into cardiac arrest – Brandon Feb 17 '16 at 19:27
  • @Brandon it was my understanding this would be better than loading all results, and looping over every line in php. Am I wrong? – Neograph734 Feb 17 '16 at 19:28
  • 1
    No, you're right. Do it with the database. I just meant that I actually tried to set this up in SQL Server and it complains. MySQL has no problem with the syntax. – Brandon Feb 17 '16 at 19:29