CODEWITHSUNDEEP
sql
user1205746
user1205746

Reputation: 3364

how do I list and classify the items that do not exist in the table

I run into road block and here is my issue:

I have a table, let's call it Items where all the items in my catalog are listed. I have ItemSold where it lists each item sold on which day. I have another table called Sales where it has the dates I want to enquiry about.

This is my illustration:

Items table enter image description here

Items Sold enter image description here

Sales enter image description here

It is obviously trivial to find out what items sold on which day. My challenge is figuring out which items not sold on which day.

I have tried:

select i.itemid from items i
where i.ItemID not in ( SELECT         i.ItemID
FROM            Sales d INNER JOIN
                         ItemSold i ON d.SaleDate = i.SaleDate)

But that only provides me with the items not sold at all.

I am looking the following result:

enter image description here

I am using MSSQL. I have attached the script to recreate the tables and data:

CREATE TABLE [dbo].[Items](
    [ItemID] [int] NULL
) ON [PRIMARY]

GO

CREATE TABLE [dbo].[ItemSold](
    [SaleDate] [date] NULL,
    [ItemID] [int] NULL
) ON [PRIMARY]

GO


CREATE TABLE [dbo].[Sales](
    [SaleDate] [date] NULL
) ON [PRIMARY]

GO

I would appreciate your help. This is a simplified version of a pretty confusing issue at work.

Upvotes: 0

Views: 43

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270091

You first generate all the possible rows -- all dates and all items. Then use left join to filter out the ones that have been sold:

select i.itemid, s.saledate
from items i cross join
     sales s left join
     itemsold si
     on si.itemid = i.itemid and si.saledate = s.saledate
where si.itemid is null;

Upvotes: 1

Related Questions