Ger rows with 2 specifical occurrences

Question

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

Does this answer your question? [SQL query: Simulating an "AND" over several rows instead of sub-querying](https://stackoverflow.com/questions/163887/sql-query-simulating-an-and-over-several-rows-instead-of-sub-querying) — astentx, Jan 13 '23 at 05:36

lemon · Answer 1 · 2023-01-12T17:11:33.287

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.

Ger rows with 2 specifical occurrences

1 Answers1