[SQL] How to query for duplicate values [duplicate]

I have a table where certain values have relationships with each other (value d is nested under value c, which is nested under group b, in separate tables). Example below:



I'm writing a query that wants to pull for situations where for the same value c, value d and group b are different. So for my query, I would want to pull the below from the above table



I want to return rows 1 and 2 because group b differs, but value c is the same. I don't want to return rows 3 and 4, because all of group b, value c, and value d have different values. I don't want to return rows 5-7 because group b is the same for each. I don't want to return row 8 because there is no additional row with the same value c, and I don't want to return rows 9 and 10 because, like rows 5-7, group b is the same for each.

The query I have now is

select *
from table1
where value c in (select value c
from table 1
group by value c
having count(value c) > 1)
and value b in (select value b
from table 1
group by value b
having count(value b) > 1)
and value d is not null
order by
value c;


It almost works, but it would return rows 5-7, since even though value b is the same value, it occurs more than once. I'm doing this in SQL Developer with a database to which I have read-only access. Thanks!

Continue reading...