CODEWITHSUNDEEP
sqloracle-database
Nestor Diaz
Nestor Diaz

Reputation: 21

Ger rows with 2 specifical occurrences

Column_A Column_B
1 X
1 Z
2 X
2 Y
3 Y
4 X
4 Y
4 Z
5 Y

I want get all distinct values of Column A that has a row with Column B equal to X and other row with Column B equal to 'Y'

The result will be like this:

Column_A
1
4

I tried in this way:

SELECT DISTINCT COLUMN_A
FROM TABLE
INNER JOIN (
   SELECT DISTINCT COLUMN_A
   FROM TABLE
   WHERE COLUMN_B = 'X') SUBTABLE 
ON TABLE.COLUMN_A = SUBTABLE.COLUMN_A
WHERE TABLE.COLUMN_B = 'Y';

I think that this solution works but isn't optimum

Thanks a have a nice day

Upvotes: 2

Views: 48

Answers (1)

lemon
lemon

Reputation: 15482

You can apply a simple aggregation by:

  • filtering only Column_B values you're interested in
  • aggregating for distinct values of Column_B
  • checking the amount of distinct values equals 2
SELECT Column_A
FROM tab
WHERE Column_B IN ('X', 'Y')
GROUP BY Column_A
HAVING COUNT(DISTINCT Column_B) = 2

or you can use the INTERSECT operator between:

  • the records having Column_B = 'X'
  • the records having Column_B = 'Y'
SELECT DISTINCT Column_A FROM tab WHERE Column_B = 'X'
INTERSECT 
SELECT DISTINCT Column_A FROM tab WHERE Column_B = 'Y'

Check the demo here.

Upvotes: 3

Related Questions