CODEWITHSUNDEEP
sql
SQL_Developer
SQL_Developer

Reputation: 3

How to fetch the Group that Contains only some specific Items in SQL?

We have some records in a table. Different Items are linked with a particular group. As shown below, Item 20,21 belongs to GroupId 1, while Items 20,21,5 belongs to another GroupId 2.

     Id   GroupId    Item
-------------------------
 1   1      1        20
 2   2      1        21
 3   3      2        20
 4   4      2        21
 5   5      2         5

I want to fetch the GroupId from above table that only contains Item 20 and 21. So in above example it should return GroupId 1.

Can someone please suggest a way how to fetch the GroupId 1 ?

Below is the script to create and populate the Data table with above records

CREATE TABLE Data
(
     Id INT IDENTITY, 
     GroupId INT, 
     Item INT 
)

INSERT INTO Data 
VALUES (1, 20), (1, 21), (2, 20), (2, 21), (2, 5)

Upvotes: 0

Views: 109

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522817

Aggregation by GroupId is one option here:

SELECT GroupId
FROM Data
GROUP BY GroupId
HAVING COUNT(CASE WHEN Item NOT IN (20, 21) THEN 1 END) = 0;

See the demo running below.

Demo

Upvotes: 1

Related Questions