SQL: Select rows that have specfic values, independent from the column

Question

I have the following tabel in a vertica DB:

+-----+-------+-------+-------+
| tid | Item1 | Item2 | Item3 |
+-----+-------+-------+-------+
|   1 | A     | B     | C     |
|   2 | B     | D     | A     |
|   3 | C     | D     | A     |
|   4 | D     | B     | A     |
+-----+-------+-------+-------+

I want to find all rows that have the Items A and B, resulting in a table:

+-----+-------+-------+-------+
| tid | Item1 | Item2 | Item3 |
+-----+-------+-------+-------+
|   1 | A     | B     | C     |
|   2 | B     | D     | A     |
|   4 | D     | B     | A     |
+-----+-------+-------+-------+

The order of how A and B occurs is random. Also this is an abstract example, I might want to look for three of four items or even just one item. Is there a simple way checking each row if it contains certain items independant of the order?

Answer 1

select      *

from        mytable

where       'A' in (Item1,Item2,Item3)
        and 'B' in (Item1,Item2,Item3)

Answer 2

Another approach might be concatenating the columns into a string, and searching that column with INSTR:

SELECT a.tid, a.Items
FROM 
    (SELECT tid, CONCAT(Item1, Item2, Item3) AS Items
    FROM MyTable) AS a
WHERE
    INSTR(a.Items, "A") > 0
    AND
    INSTR(a.Items, "B") > 0

etc.

Columns in CONCAT and WHERE condition can be extended as needed

Answer 3

mysql> select * from vertica where (item1 in("A") and (item2 in("B") or item3 in
("B"))) or (item1 in("B") and (item2 in("A") or item3 in("A"))) or (item2 in("A"
) and (item1 in("B") or item3 in("B"))) or (item2 in("B") and (item1 in("A") or
item3 in("A"))) or (item3 in("A") and (item1 in("B") or item2 in("B"))) or (item
3 in("B") and (item1 in("A") or item2 in("A")));

O/P ->
+-----+-------+-------+-------+
| tid | item1 | item2 | item3 |
+-----+-------+-------+-------+
|   1 | A     | B     | C     |
|   2 | B     | D     | A     |
|   4 | D     | B     | A     |
+-----+-------+-------+-------+
3 rows in set (0.02 sec)

Answer 4

TRY THIS:

 SELECT * FROM TAB1 WHERE (ITEM1 IN ('A','B') AND ITEM2 IN ('A','B')) 
                            OR (ITEM1 IN ('A','B') AND ITEM3 IN ('A','B')) 
                            OR (ITEM2 IN ('A','B') AND ITEM2 IN ('A','B'))