Search for Pairs of Values in mysql

In my Table i have two IDs id1 and id2

there might be duplicats like:

1 / 1
1 / 2
2 / 1
2 / 2
2 / 2

Now i want to search for defined tuples in 开发者_JS百科database like: I want all data for tuples (1,1) and (2,1) and (1,2)

If I have only one id there was the nice way of searching with something like:

WHERE id1 IN (1,2,3)

If I copy it for both fields, that would be:

id1 in (1,2)
id2 in (1,2)

But this would obviously also select (2,2) which ist not wanted.

Is it possible to do something like this with the tupels? (Backup solution is a loop with one query per tupel)

CREATE TABLE test (
id INT UNSIGNED NOT NULL auto_increment,
pair_id INT UNSIGNED NOT NULL,
PRIMARY KEY(id, pair_id)
) ENGINE = InnoDB;

INSERT INTO test (pair_id) VALUES (1), (1), (1), (1), (2), (2), (2), (2), (3), (3), (3), (3);

SELECT * FROM test WHERE (id, pair_id) IN ((1,1), (2,1), (3,1), (4,2));

That's how I'd construct the table and the query in case of tuples you described. Tested it at MySQL 5.1, it works, it returns first 3 records.

In don't mysql here right now to test, but I believe the following should work

select *
  from yourTable
 where concat(cast(id1 as char), '/', cast(id2 as char)) in ('1/1', '1/2', '3/1');

In this case, you want to search for tuples 1/1 or 1/2 or 3/1

or if id1 and id2 are constrained between [0, 9], then you can also

select *
  from yourTable
 where id1*10 + id2 in (11, 12, 31);

and a not clause:

where id1 in (1,2)
and id2 in (1,2)
and not (id1 = 2 and id2 = 2)