SQL WHERE question - Additional criteria for one column-CodePudding

I'm probably overthinking this but it's driving me nuts:

I'm setting up a WHERE statement to pull some specific data and there is one column that is the main criteria and another column that is a sub criteria. There are 6 values I need to pull from the main criteria column but for only one of those values I need to go down to the sub criteria level.

CRITERIA	SUBCRITERIA
ITEM1	SUBITEM
ITEM2	SUBITEM
ITEM3	SUBITEM
ITEM4	SUBITEM
ITEM5	SUBITEM
ITEM6	SUBITEM2
ITEM6	SUBITEM1
ITEM6	SUBITEM2
ITEM7	SUBITEM
ITEM8	SUBITEM

I want to pull where CRITERIA IN ('ITEM1','ITEM2','ITEM3','ITEM4','ITEM5','ITEM6')

But for ITEM6 I only want where SUBCRITERIA = 'SUBITEM1'.

Whatever I try doesn't pull exactly what I want. I think I'm just blanking on something basic with the syntax.

CodePudding user response：

You may use an OR condition in the WHERE clause:

SELECT CRITERIA, SUBCRITERIA
FROM yourTable
WHERE CRITERIA IN ('ITEM1', 'ITEM2', 'ITEM3', 'ITEM4', 'ITEM5') OR
      (CRITERIA = 'ITEM6' AND SUBCRITERIA = 'SUBITEM1');