SELECT only orders which have corresponding items

[Ach005ki]

Hi all
I'm really struggling with this query:

To only select sales orders where the ordered items are in a particular list of items.

In the simplified example below, I would want to select only sales orders containing apples or oranges but not those orders containing any other fruit (regardless of whether apples or oranges were also ordered); my code would therefore need to return only orders 100224, 103744 & 104041.

[highlight]Tables[/highlight]

[tt][blue]
Customer

store
------------
grocer
greengrocer
minimarket
supermarket
concession
tuck-shop
[/blue]
============
[green]
Produce

fruit
------------
apples
pears
oranges
bananas
nectarines
strawberries
blueberries
raspberries
blackberries
[/green]
============
[purple]
Orders

fruit store order_ref
------------ ------------ ---------
apples greengrocer 100001
bananas greengrocer 100001
strawberries greengrocer 100001
nectarines greengrocer 100001
apples minimarket 100224
oranges minimarket 100224
apples supermarket 103052
pears supermarket 103052
oranges supermarket 103052
bananas supermarket 103052
nectarines supermarket 103052
strawberries supermarket 103052
blueberries supermarket 103052
raspberries supermarket 103052
blackberries supermarket 103052
apples tuck-shop 103744
raspberries grocer 104207
strawberries grocer 104207
blackberries grocer 104207
nectarines greengrocer 105326
bananas greengrocer 105326
oranges greengrocer 105326
strawberries greengrocer 105326
oranges concession 104041
[/purple][/tt]

I thought this would be relatively easy but I'm in serious need of assistance and would be very grateful for any hints, tips or solutions.

Mark, somewhere near Blackburn Lancs!

 

[gmmastros]

There's likely to be many different ways to accomplish this query, so you may want to test all of the suggestions.

Here's one:

Declare @Orders Table(Fruit VarChar(20), Store VarChar(20), Order_Ref Int)

Insert Into @Orders Values('apples'        ,'greengrocer'  ,100001)    
Insert Into @Orders Values('bananas'       ,'greengrocer'  ,100001)   
Insert Into @Orders Values('strawberries'  ,'greengrocer'  ,100001)    
Insert Into @Orders Values('nectarines'    ,'greengrocer'  ,100001)    
Insert Into @Orders Values('apples'        ,'minimarket'   ,100224)    
Insert Into @Orders Values('oranges'       ,'minimarket'   ,100224)    
Insert Into @Orders Values('apples'        ,'supermarket'  ,103052)    
Insert Into @Orders Values('pears'         ,'supermarket'  ,103052)    
Insert Into @Orders Values('oranges'       ,'supermarket'  ,103052)    
Insert Into @Orders Values('bananas'       ,'supermarket'  ,103052)    
Insert Into @Orders Values('nectarines'    ,'supermarket'  ,103052)    
Insert Into @Orders Values('strawberries'  ,'supermarket'  ,103052)    
Insert Into @Orders Values('blueberries'   ,'supermarket'  ,103052)    
Insert Into @Orders Values('raspberries'   ,'supermarket'  ,103052)    
Insert Into @Orders Values('blackberries'  ,'supermarket'  ,103052)    
Insert Into @Orders Values('apples'        ,'tuck-shop'    ,103744)    
Insert Into @Orders Values('raspberries'   ,'grocer'       ,104207)    
Insert Into @Orders Values('strawberries'  ,'grocer'       ,104207)    
Insert Into @Orders Values('blackberries'  ,'grocer'       ,104207)    
Insert Into @Orders Values('nectarines'    ,'greengrocer'  ,105326)    
Insert Into @Orders Values('bananas'       ,'greengrocer'  ,105326)    
Insert Into @Orders Values('oranges'       ,'greengrocer'  ,105326)    
Insert Into @Orders Values('strawberries'  ,'greengrocer'  ,105326)    
Insert Into @Orders Values('oranges'       ,'concession'   ,104041)    

Select	Order_Ref
From	@Orders
Group BY Order_Ref
Having  Count(*) = Count(Case When Fruit = 'Apples' Then 1 End) 
                   + Count(Case When Fruit = 'oranges' Then 1 End)

Note that I create a table variable with your sample data to test the query.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[markros]

Just to give you a bit of theoretical background. This problem you're presenting is called 'Relational Division' There are numerous approach to its solution and I know of two recent blogs by Peter Larsson in regards to it
Relational Algebra

PluralSight Learning Library

 

[Ach005ki]

Hi George,

Thank you for your swift assistance.

I appreciate the effort taken - unfortunately my explanation of the problem and simplified example may have been a little misleading.

The actual number of corresponding items is 144 items (the number of other possible items is in their 1000s).

I had created a temp table to hold the relevant corresponding item codes but can't seem to construct a query which will return only those orders which contain * only * items from the corresponding items list.

Does this make sense?

it may be a little clearer on examination of the attached excel file which contains 3 tabs:
[ul]
[li]a tab for a list of order lines (restricted to only those orders which contain a corresponding item & possibly other items)[/li]
[li]a tab for the corresponding item list[/li]
[li]a tab for a quick pivot showing a summary of the order number and whether it contains items which are not on the corresponding item list[/li]
[/ul]
Obviously I can work the data within excel to determine which orders contain only those corresponding items, but the problem's taken hold of me now & I really would like to be able to return the required data from within SQL without resorting to excel spreadsheet work.

Again, any further assistance would be very much appreciated.

Kind Regards


Mark / Ach005ki


Mark, somewhere near Blackburn Lancs!

 

[gmmastros]

Here's another query you may want to try. The trick here is to use a left join because this returns all rows from the orders table and corresponding rows from the CorrespondingItems table. If there is no match, the data in the column(s) from Corresponding Items will be null. We then count the number of Non-Null items in each table and only return the orders where the count is the same. This is the same as only returning orders where all of the items in the order table match items in the corresponding items table.

Here's the code:

Declare @Orders Table(Fruit VarChar(20), Store VarChar(20), Order_Ref Int)

Insert Into @Orders Values('apples'        ,'greengrocer'  ,100001)    
Insert Into @Orders Values('bananas'       ,'greengrocer'  ,100001)   
Insert Into @Orders Values('strawberries'  ,'greengrocer'  ,100001)    
Insert Into @Orders Values('nectarines'    ,'greengrocer'  ,100001)    
Insert Into @Orders Values('apples'        ,'minimarket'   ,100224)    
Insert Into @Orders Values('oranges'       ,'minimarket'   ,100224)    
Insert Into @Orders Values('apples'        ,'supermarket'  ,103052)    
Insert Into @Orders Values('pears'         ,'supermarket'  ,103052)    
Insert Into @Orders Values('oranges'       ,'supermarket'  ,103052)    
Insert Into @Orders Values('bananas'       ,'supermarket'  ,103052)    
Insert Into @Orders Values('nectarines'    ,'supermarket'  ,103052)    
Insert Into @Orders Values('strawberries'  ,'supermarket'  ,103052)    
Insert Into @Orders Values('blueberries'   ,'supermarket'  ,103052)    
Insert Into @Orders Values('raspberries'   ,'supermarket'  ,103052)    
Insert Into @Orders Values('blackberries'  ,'supermarket'  ,103052)    
Insert Into @Orders Values('apples'        ,'tuck-shop'    ,103744)    
Insert Into @Orders Values('raspberries'   ,'grocer'       ,104207)    
Insert Into @Orders Values('strawberries'  ,'grocer'       ,104207)    
Insert Into @Orders Values('blackberries'  ,'grocer'       ,104207)    
Insert Into @Orders Values('nectarines'    ,'greengrocer'  ,105326)    
Insert Into @Orders Values('bananas'       ,'greengrocer'  ,105326)    
Insert Into @Orders Values('oranges'       ,'greengrocer'  ,105326)    
Insert Into @Orders Values('strawberries'  ,'greengrocer'  ,105326)    
Insert Into @Orders Values('oranges'       ,'concession'   ,104041)    

Declare @CorrespondingItems Table(Fruit VarChar(20))
Insert Into @CorrespondingItems Values('apples')
Insert Into @CorrespondingItems Values('oranges')


Select Order_Ref
From   @Orders O
       Left Join @CorrespondingItems CI
         On O.Fruit = CI.Fruit
Group By Order_Ref
Having Count(O.Fruit) = Count(CI.Fruit)

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[markros]

Try another query which works the same way as George's.

Select Order_Ref from @Orders 
EXCEPT
select Order_Ref from @Orders O 
where not exists (select 1 from @CorrespondingItems CI where Ci.Fruit = O.Fruit )

PluralSight Learning Library