[SOLVED] Teradata sql two conditions (and) to check multiple rows to determine validity

Teradata sql two conditions (and) to check multiple rows to determine validity

Suppose I have a table with apple names and the color in another column:

Variety        Color
----------------------
Fuji           Red
Fuji           Golden
Granny Smith   Green
Granny Smith   Red
Gala           Red
Gala           Golden
Gala           Green

I want to know which apple varieties have both Red and Golden.So the answer that I am expecting is Fuji and Gala. So how do I write the query. Of course this query does not work.

Select variety 
from table 
where Color like all ('Golden%', 'Red%')

My table has many columns and a huge table similar to this.

Solution

You could use INTERSECT:

INTERSECT Operator

Returns only the rows that exist in the result of both queries.

SELECT variety
FROM tab
WHERE color = 'Red'
INTERSECT
SELECT variety
FROM tab
WHERE color = 'Golden'