CODEWITHSUNDEEP
sqlsql-serverstored-procedures
Liam neesan
Liam neesan

Reputation: 2551

How to find the record which is not exists with some criteria in SQL Server?

I have two tables.

ItemRelation table having 30k records

ID     ChildID1     ChildID2     ChildID3
------------------------------------------
9      null         null          null
49     43           50                       //43 in childid1, don't want this record too
111    112          113           null
65     68           null          null
222    221          223           224
79     null         null          null
5773   5834         5838          null

F_ItemDailySalesParent having millions of records

ItemID              StoreId
-----------------
9         1001   //ItemID 9,41,5773 belongs to 1001 StoreID
41        1001   
43        1400   //ItemID 43,45,65,5834 belongs to 1400 StoreID
45        1400
65        1400
68        2000   //ItemID 68,79 belongs to 2000 StoreID
79        2000
5773      1001
5834      1400
5838      2000

I want to show the record ID from ItemRelation table where the ItemID from F_ItemDailySalesParent not present in ItemRelation

ItemID    StoreID
-----------------
49        1001
111       1001
65        1001
222       1001
79        1001

9         1400
111       1400
222       1400
79        1400

9         2000
49        2000
111       2000
222       2000
5773      2000

I tried this following query. But this will work without StoreID. But no idea for the above result

select ID from HQMatajer.dbo.ItemRelation ir
where not exists(
    select ID,StoreID
    from [HQWebMatajer].[dbo].[F_ItemDailySalesParent] Fid
    where fid.ItemID=ir.ID 
        or fid.ItemID = ir.ChildID1 
        or Fid.ItemID=ir.ChildID2 
        or Fid.ItemID=ir.ChildID3 
        and time between '2017-01-01 00:00:00.000' and '2017-02-28 00:00:00.000'
     group by ItemID,StoreID
  )

Update

I have Hqmatajer.dbo.Store that column name of storeCode = F_ItemDailySalesParent.Storeid

Upvotes: 0

Views: 46

Answers (3)

SqlZim
SqlZim

Reputation: 38033

Include checking if StoreId matches when using the not exists()

select ID 
from HQMatajer.dbo.ItemRelation ir
cross join (select distinct storeCode from Hqmatajer.dbo.Store) s
where not exists(
    select 1
    from [HQWebMatajer].[dbo].[F_ItemDailySalesParent] Fid
    where fid.StoreId = s.StoreCode
     and [time] between '2017-01-01 00:00:00.000' and '2017-02-28 00:00:00.000'
     and ( fid.ItemID=ir.ID 
        or fid.ItemID=ir.ChildID1 
        or Fid.ItemID=ir.ChildID2 
        or Fid.ItemID=ir.ChildID3 
        )
  )

Upvotes: 1

JBrooks
JBrooks

Reputation: 10013

First get a unique list of ItemIds and unique list of StoreIDs, then you can see which are missing with a left join and a where cross ref table id is null. I'll do it in generic terms so you get the idea:

select s.StoreId, i.ItemId
from Stores s
cross apply Items i
left join ItemRelation ir
on s.StoreId = ir.StoreId
and i.ItemId = ir.ItemId
where ir.Id is null

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270081

If I understand correctly, you want to start with a list of all stores and items and then filter out the ones that are present.

select i.id, s.storeId
from (select distinct id from HQMatajer.dbo.ItemRelation ir) i cross join
     stores s  -- assume this exists
where not exists (select 1
                  from [HQWebMatajer].[dbo].[F_ItemDailySalesParent] idsp
                  where idsp.ItemID = i.ID and idsp.storeId = s.storeId
                 ) and
      not exists (select 1
                  from [HQWebMatajer].[dbo].[F_ItemDailySalesParent] idsp
                  where idsp.ItemID = i.childID1 and idsp.storeId = s.storeId
                 ) and
      not exists (select 1
                  from [HQWebMatajer].[dbo].[F_ItemDailySalesParent] idsp
                  where idsp.ItemID = i.childID2 and idsp.storeId = s.storeId
                 ) and
      not exists (select 1
                  from [HQWebMatajer].[dbo].[F_ItemDailySalesParent] idsp
                  where idsp.ItemID = i.childID3 and idsp.storeId = s.storeId
                 );

I did not include the time condition. It is not in your sample data, so it is unclear where it fits.

Upvotes: 0

Related Questions