Select an included value

advertisements

I'm using Oracle SQL and i need help with a query. Hope it's not too much easy one. I did't find an answer for it in Google.

I have a table that need to be aggregated by ID column and then to select only the records that two values are included in a certain table (and both of them).

Table for example

ID | Value
1  |  Y
1  |  N
2  |  N
2  |  N
2  |  Y
3  |  Y
3  |  Y
4  |  Y
5  |  Y
5  |  N
5  |  Y
5  |  N

The output table need to include only the IDs that both Y and N are included in Value table. Output:

ID
1
2
5


Another solution that groups by the ID and uses HAVING to return only those with > 1 DISTINCT values:

with v_data(id, value) as (
  select 1, 'Y' from dual union all
  select 1, 'N' from dual union all
  select 2, 'Y' from dual)
select id
from v_data
group by id
having count(distinct value) > 1