CODEWITHSUNDEEP
sqlsql-servert-sql
WJProctor
WJProctor

Reputation: 71

SQL In List and Only In List

I have a strange TSQL question which should be really simple, although i cant work out how to right it.

Let's suppose I have a table with Box ID | Item ID

+--------+---------+
| Box ID | Item ID |
+--------+---------+
|      1 |       1 |
|      1 |       2 |
|      1 |       3 |
|      2 |       1 |
|      2 |       2 |
|      2 |       3 |
|      2 |       4 |
|      3 |       1 |
|      3 |       2 |
|      3 |       3 |
|      3 |       4 |
|      3 |       5 |
+--------+---------+

I have a list of items 1,2,3 and I want to know only the kit that has only those items in. I.e. Kit 1. Obviously an In will give me anything that included. I dunno if its work doing a count to see how many are in and out.

Any ideas are greatly appreciated.

So I'm nearly there thanks to everyone. I have expanded this out to.

DECLARE @Instances AS TABLE(PlateID INT);

INSERT INTO @Instances(PlateID)VALUES(11638),(11637),(11632),(11659)

DECLARE @NumberofPlates INT;
SELECT @NumberofPlates = COUNT(*) FROM @Instances;

SELECT Instance_Plate_Room_Instance_ID_LNK 
from dbo.M_Instance_Plate 
WHERE Instance_Plate_Deleted = 0 
group by Instance_Plate_Room_Instance_ID_LNK
having sum(case when Instance_Plate_Plate_ID_LNK not in (SELECT PlateID FROM 
@Instances) then 1 else 0 end) = 0 and
   SUM(case when Instance_Plate_Plate_ID_LNK in (SELECT PlateID FROM 
@Instances) then 1 else 0 end) = @NumberofPlates;

Any tips on getting round Cannot perform an aggregate function on an expression containing an aggregate or a subquery. On the Select PlateID From @Instances code.

Upvotes: 0

Views: 1103

Answers (2)

djangojazz
djangojazz

Reputation: 13170

I would just hunt for the distinct possibilities of what you do not want in a set and then eliminate that set. Like so:

DECLARE @Data TABLE (BoxId INT, ItemId Int);

INSERT INTO @Data VALUES (1, 1), (1, 2),(1, 3),(2, 1),(2, 2),(2, 3),(2, 4),(3, 1),(3, 2),(3, 3),(3, 4),(3, 5)

--as is
Select *
From @Data

--Look up a set that is what you do not want, seperate it(I just did a nested select in a where clause, you can do a CTE, table variable, whatevs)
SELECT distinct BoxId
From @Data
WHERE BoxId NOT IN (Select BoxId From @Data WHERE ItemId IN (4,5))

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1271013

You can use group by and having:

select boxid
from t
group by boxid
having sum(case when itemid not in (1, 2, 3) then 1 else 0 end) = 0 and
       sum(case when itemid in (1, 2, 3) then 1 else 0 end) = 3;

Note that the second condition depends on two factors:

  • Are there duplicates? If so, the logic would have to change slightly.
  • Do you really want all three items? Or is it enough to just have no non-1,2,3 items? If the latter, then remove the second condition.

Upvotes: 1

Related Questions