February 28, 2008 at 10:27 am
I have to write a query and am a little stuck.
I have a products table and an orders table and orderLines table.
I have been given a small subset of products from the products table.
I have to find all orders that contain one or more of the subset of products, along with skus that are not within the subset provided ( basically any mixed orders).
I can find all orders containing any of the subset of products, but i cannot get only the orders that contain any of the subset of products, along with any products that are not in the subset.
Any help would be greatly appriciated.
February 28, 2008 at 10:38 am
Can you please provide table ddl, sample data, and the desired results? It clarifies the problem and makes it easier on everyone trying to help.
Thanks.
February 28, 2008 at 11:05 am
sure. Below is a simplified version of the data.
There is an order table. an order table can have many orderLines.
There is a product table containing all orderable products.
there is a productSubset table containing the subset of products that should be on the orders i have to find.
My query should return OrderID 1 and 5 as it contains products both in the productSubset table and in the product table
It will not return OrderID 2,4 and 6 as they only contains products in the Product Table
It will not return orderID 3 as it only contains products in the ProductSubset table
Order table
OrderID_____OrderName
1___________a
2___________b
3___________c
4___________d
5___________e
6___________f
Product Table
ProductID___________Productname
1001_______________Product a
1002_______________Product b
1003_______________Product c
1004_______________Product d
1005_______________Product e
1006_______________Product f
1007_______________Product g
1008_______________Product h
1009_______________Product i
1010_______________Product j
ProductSubset table
ProductID
1001
1002
1004
OrderLine table
OrderLineID_____OrderID_____ProductID_____Quantity
100____________1__________1001___________1
101____________1__________1003___________1
102____________1__________1004___________1
103____________2__________1010___________1
104____________2__________1009___________1
105____________3__________1001___________1
106____________3__________1002___________1
107____________4__________1004___________1
108____________5__________1001___________1
109____________5__________1006___________1
110____________6__________1009___________1
If a value is a letter, assume its varchar(10)
If a value is a number assume its an int. I left out prices to keep it as simple as possible.
Apologies for the underscores between the values in the tables, spaces keep being removed when i post this live
Thanks all
February 28, 2008 at 11:21 am
So - if I understand you - you want to return all orders (and all of their line items) if the lineitems include any of the product ID's you list?
Select O.*, ol.*
from Orders O
inner join OrderlineItems OL on o.orderid=ol.orderID
where
exists (select *
from OrderLineItem OL_inner
inner join ProductSubSet ps on ps.productID=ol_inner.productID
where ol_inner.orderID=o.orderID)
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
February 28, 2008 at 11:23 am
not exactly.
The query must return orderIDs of orders that contain both products in the subset and not in teh subset.
Any orders that ONLY contain orders in the subset are not required.
Any orders that ONLY contain orders NOT in the subset are not require.
February 28, 2008 at 11:27 am
sorry, forgot to say, I was wondering if its possible with joins alone, rather than using a exists? If a contains is necessary, then so be it
February 28, 2008 at 11:34 am
Hmm - I think this give you what you wish (not sure it's all that performant)
Select O.* from Orders O
where
exists (
select null
from OrderLineItem OL_inner
left outer join ProductSubSet ps on ps.productID=ol_inner.productID
where ol_inner.orderID=o.orderID
GROUP BY ol_inner.orderID
HAVING min(case when ps.productID is null then 0 else -1 end)=-1 and
max(case when ps.productID is null then 0 else -1 end)=0
)
This one might work better:
Select O.* from Orders O
where
exists (
select null
from OrderLineItem OL_inner
left outer join ProductSubSet ps on ps.productID=ol_inner.productID
where ol_inner.orderID=o.orderID and ps.productID is null
)
and exists (
select null
from OrderLineItem OL_inner
inner join ProductSubSet ps on ps.productID=ol_inner.productID
where ol_inner.orderID=o.orderID
)
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
February 28, 2008 at 1:49 pm
This is a method that works much like the one Matt posted, but does not use exists.
Select O.OrderID, O.OrderName
FROM @Order O
INNER JOIN
(
SELECT OL.OrderId
FROM @OrderLine OL
LEFT JOIN @ProductSubSet PS
ON PS.ProductID=OL.ProductID
WHERE PS.productID IS NULL
) AS a
ON o.Orderid = a.Orderid
INNER JOIN
(
SELECT DISTINCT OL.orderId
FROM @OrderLine OL
INNER JOIN @ProductSubSet PS
ON PS.ProductID=OL.ProductID
) AS b
ON o.orderid = b.orderid
February 29, 2008 at 4:29 am
perfect!! thanks a mil for the help guys.
I have learned lots from this exercise and the code you provided. its much appriciated.
Defo something im storing for reference at a later date.
Thanks all
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply