Search code examples
sqlteradatateradata-sql-assistant

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

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. ofcourse this query does not work.

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

My table has many columns and a huge table similiar 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'