Search code examples
oracle-databaseplsqloracle11g

How can I select id which have 'Red' and at the same time 'Black'?

I want to write a simple pl/sql or mssql

For selecting the car id which is red and at the same time black.

sample:

ID | Color  
 1 | Red
 1 | Black
 2 | Brown
 2 | Red
 3 | Black
 3 | Yellow

What i want is:

ID
1
Solution

  • Here is a SQL standard way that works in either database:

    select id
from sample s
group by id
having sum(case when color = 'red' then 1 else 0 end) > 0 and
       sum(case when color = 'black' then 1 else 0 end) > 0;

    Each condition in the having clause counts the number of rows that match one of the colors. Only ids that have each color will be included in the final result set.

    EDIT:

    You can add where color in ('red', 'black') before the group by. Depending on the data and indexes, this could significantly improve the performance of the query.