topic Re: ABAP SQL Select rows where a field has all specified values in Application Development and Automation Discussions

ABAP SQL Select rows where a field has all specified values

former_member355261 — Mon, 29 May 2023 15:24:07 GMT

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 ?

Re: ABAP SQL Select rows where a field has all specified values

Yogananda — Mon, 29 May 2023 15:45:29 GMT

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.

Re: ABAP SQL Select rows where a field has all specified values

Sandra_Rossi — Mon, 29 May 2023 15:50:07 GMT

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).