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.
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)
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)
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
