Select rowst where ID is on list and meet 2 other requirement, probably subquery in Teradata SQL?

Question

I have table in Teradata SQL like below:

ID   | col1 | col2
-------------------
111  | A  | 54
222  | B  | 8
333  | C  | 17
444  | B  | 44
555  | A  | 1

And I have list of ID of clients:

myList = ['111', '222', '333']

And I need to select ID of clients from table which are on myList and meet requirements:

1. In col1 value is "A" or "B"
2. In col2 value is bigger than 10 (>10)

So as a result I need like below:

ID   | col1 | col2
--------------------
111  | A    | 54

Because ID = 111 and is on myList, col1 is "A" or "B" and in col2 is value bigger than 10.

How can I do that in Teradata SQL ? Probably in subquery ?

Answer 1

There is no need for a subquery.
All conditions can be combined with the AND operator in the WHERE clause of a simple SELECT statement:

SELECT * 
FROM tablename
WHERE ID IN ('111', '222', '333')
  AND col1 IN ('A', 'B')
  AND col2 > 10

Answer 2

We can try an aggregation approach here:

SELECT *
FROM yourTable
WHERE ID IN ('111', '222', '333') AND
      ID IN (SELECT ID
             FROM yourTable
             GROUP BY ID
             HAVING COUNT(CASE WHEN col1 NOT IN ('A', 'B') THEN 1 END) = 0 AND
                    COUNT(CASE WHEN col2 > 10 THEN 1 END) > 0);

The WHERE clause restricts ID values to your client list. The two conditions in the HAVING clause check for the restrictions on the two other columns.