Query items that don't have a related record in link table but return results with Id from link-CodePudding

I have an Item table:

Id | Title  | Active
====================
 1 | Item 1 | 1
 2 | Item 2 | 1

A Location table:

Id | Name
=========
 1 | A1
 2 | B1

and a link table, where EventId specifies a cycle count event:

Id | EventId | ItemId | LocationId
=============|====================
1  | 1       | 1      | 2
2  | 1       | 2      | 1
3  | 2       | 1      | 1
4  | 2       | 2      | 2
5  | 3       | 1      | 1

I need to determine what items haven't been cycle-counted for a specified EventId (which in this example would be ItemId 2 for EventId 3). We're using a code generation tool that only supports tables and views with a simple filter, so I can't use a sproc or table-valued function. Ideally we'd like to be to do this:

SELECT [EventId], [ItemId] FROM [SomeView] WHERE [EventId] = 3

and get a result like

EventId | ItemId
================
3       | 2

I've tried to wrap my head around this -- unsuccessfully -- because I know it's difficult to query a negative. Is this even possible?

CodePudding user response：

Is something like the following what you're after?

select l.eventId, x.Id ItemId
from Link l
cross apply (
    select *
    from Items i
    where i.Id != l.ItemId
)x
where l.EventId = 3;

CodePudding user response：

--data to work with
DECLARE @items TABLE (ID int, Title nvarchar(100), Active int)
INSERT INTO @items VALUES (1, 'Item 1', 1) 
INSERT INTO @items VALUES (2, 'Item 2', 1)  

DECLARE @location TABLE (ID int, Name nvarchar(100))
INSERT INTO @location VALUES (1, 'A1') 
INSERT INTO @location VALUES (2, 'B1') 

DECLARE @linkTable TABLE (ID int, EventId int, ItemId int, LocationId int)
INSERT INTO @linkTable VALUES (1, 1, 1, 2) 
INSERT INTO @linkTable VALUES (2, 1, 2, 1) 
INSERT INTO @linkTable VALUES (3, 2, 1, 1) 
INSERT INTO @linkTable VALUES (4, 2, 2, 2) 
INSERT INTO @linkTable VALUES (5, 3, 1, 1) 
INSERT INTO @linkTable VALUES (6, 4, 2, 1) 

--query you want
SELECT 3 as EventID, ID as ItemID
FROM @items i
WHERE ID not in (SELECT ItemId 
                 FROM @linkTable 
                 WHERE EventId = 3)

Get all the ItemIDs from the LinkTable and then get all the items from the Items table that dont have the sync event. You can replace the 3 in WHERE and SELECT clauses with whatever event you are looking for. And if you want all such pairs of event item then this should do it:

SELECT subData.EventId, subData.ItemID
FROM (SELECT i.ID as ItemID, cj.EventId
      FROM @items i CROSS JOIN (SELECT DISTINCT EventId 
                                FROM @linkTable) cj) subData 
left join  @linkTable lt ON lt.EventId = subData.EventId and lt.ItemId = subData.ItemID
WHERE lt.ID is null

This could be heavy on performance because CROSS JOIN and DISTINCT and subjoins but it gets the job done. At 1st you create a data of all possible items and events pairs, then left join linked table to it and if the linked table's ID is null that means that there is no event item pair which means that the item is not synced for that event.