ABAP SQL Select rows where a field has all specified values

[former_member355261]

Hello,

I have the following table:

A | B

A1 | B1

A1 | B2

A2 | B1

A2 | B2

A3 | B1

I need to select by SQL query the rows (column A) where column B has values B1 and B2 at the same time.

So here the result should be

A1
A2

Because only A1 and A2 have rows with B1 and B2
A3 has a row with B1 only so is not in the result.

How can I achieve this with SELECT statement please ?

[yogananda]

occoromomis

You can use the following SQL query to select rows where column A has values B1 and B2 at the same time:

SELECT A FROM table_name WHERE B IN ('B1', 'B2') GROUP BY A HAVING COUNT(DISTINCT B) = 2;

Copy

This query selects all rows from the table where column B has values ‘B1’ or ‘B2’, groups them by column A and then filters out the groups that do not have both values ‘B1’ and ‘B2’ by using the HAVING clause with COUNT(DISTINCT B) = 2.

I hope this helps! Let me know if you have any other questions.

[Sandra_Rossi]

SELECT DISTINCT a
  FROM table_name AS t
  WHERE EXISTS ( SELECT * FROM table_name WHERE a = t~a AND b = 'B1' )
    AND EXISTS ( SELECT * FROM table_name WHERE a = t~a AND b = 'B2' )
  INTO TABLE @DATA(itab).