Application Development Discussions
Join the discussions or start your own on all things application development, including tools and APIs, programming models, and keeping your skills sharp.
cancel
Showing results for 
Search instead for 
Did you mean: 

ABAP SQL Select rows where a field has all specified values

former_member355261
Participant
0 Kudos
804

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 ?

1 ACCEPTED SOLUTION

yogananda
Product and Topic Expert
Product and Topic Expert
0 Kudos
675

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.

2 REPLIES 2

yogananda
Product and Topic Expert
Product and Topic Expert
0 Kudos
676

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
Active Contributor
0 Kudos
675
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).