Retrieve sequence of data from different columns.

Question

Let's say I have a table containing following data:

| id | t0 | t1 | t2 |
______________________
| 1  |  4 |  5 |  6 |
| 2  |  3 |  5 |  2 |
| 3  |  6 |  4 |  5 |
| 4  |  4 |  5 |  9 |
| 5  | 14 |  5 | 49 |

I want to retrieve all the rows containing 4, 5, 6 (regardless the position of numbers in the tables), so row 1 & row 3 will be selected. How to do that with SQL query?

The table contains thousand of records.

Answer 1

You can always do it the "hard" way:

select * 
  from tbl 
 where (t0 = 4 AND t1 = 5 AND t2 = 6)
    or (t0 = 5 AND t1 = 6 AND t2 = 4)
    or (t0 = 6 AND t1 = 4 AND t2 = 5)

Answer 2

You can do it like this:

select *
 from table
where 4 in (t0, t1, t2) 
  and 5 in (t0, t1, t2) 
  and 6 in (t0, t1, t2)

Answer 3

Another "hard" way:

DECLARE @table TABLE (id int, t0 int, t1 int, t2 int)

INSERT INTO @table(id, t0, t1, t2) VALUES(1, 4, 5, 6)
INSERT INTO @table(id, t0, t1, t2) VALUES(2, 3, 3, 2)
INSERT INTO @table(id, t0, t1, t2) VALUES(3, 6, 4, 5)
INSERT INTO @table(id, t0, t1, t2) VALUES(4, 4, 5, 5)

SELECT * 
FROM @table
WHERE (t0+t1+t2) = 15
    AND t0 BETWEEN 4 AND 6
    AND t1 BETWEEN 4 AND 6
    AND t2 BETWEEN 4 AND 6
    AND t0 <> t1