Tricky SQL Statement - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Tricky SQL Statement

Author

Topic

jamesallen74
Starting Member

4 Posts

Posted - 2011-03-05 : 11:40:33

Ok, tricky for me, but it's probably obvious to you guys.

Suppose I have an Order table,
a Product Table,
and an Order_Product table
(for the items selected for each unique order).

How do I Select a list of Orders that have BOTH (not OR) Product1 and Product3 selected in their order? Here is what I have and it's not returning what I expect:

SELECT

Order.OrderID,
Order.OrderName,
OrderProduct.ProductID

FROM Order
INNER JOIN OrderProduct
ON Order.OrderID = OrderProduct.OrderID
INNER JOIN Product
ON OrderProduct.ProductID = Product.ProductID

WHERE
(
OrderProduct.ProductID=1 AND
OrderProduct.ProductID=3
)

Do I need a nested SQL statement, or a LEFT OUTER JOIN or something?

THANK YOU!!!

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2011-03-05 : 11:51:14

Havn't tested. Check if this is what you are looking for

Select
O.OrderID,
O.OrderName,
OP.ProductID
From dbo.[Order] O
Inner Join (Select OrderId From OrderProduct where ProductID in(1,3) group by OrderId having COUNT(ProductID)=2) B On O.OrderId=B.OrderID
Inner Join OrderProduct oP on OP.OrderId=B.OrderID
Inner Join Product P on P.ProductID=OP.ProductID
Where P.ProductId in (1,3)

Cheers
MIK

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2011-03-05 : 11:51:16

Try this:

SELECT
[Order].OrderID,
[Order].OrderName,
Product.ProductID
FROM [Order]
inner join OrderProduct on OrderProduct.OrderID = [Order].OrderID
INNER JOIN Product ON OrderProduct.ProductID = Product.ProductID
Inner join (Select OrderID,count(Distinct OrderID) As Cnt
FROM OrderProduct group by OrderID having count(*) > 1) as OP
on OrderProduct.OrderID = OP.OrderID

jamesallen74
Starting Member

4 Posts

Posted - 2011-03-05 : 12:13:17

quote:
Originally posted by pk_bohra

Try this:

SELECT
[Order].OrderID,
[Order].OrderName,
Product.ProductID
FROM [Order]
inner join OrderProduct on OrderProduct.OrderID = [Order].OrderID
INNER JOIN Product ON OrderProduct.ProductID = Product.ProductID
Inner join (Select OrderID,count(Distinct OrderID) As Cnt
FROM OrderProduct group by OrderID having count(*) > 1) as OP
on OrderProduct.OrderID = OP.OrderID

PK, where in this statement does it indicate to look for Product1 and Product3 in the OrderProduct table?

jamesallen74
Starting Member

4 Posts

Posted - 2011-03-05 : 12:15:31

quote:
Originally posted by MIK_2008

Havn't tested. Check if this is what you are looking for

Select
O.OrderID,
O.OrderName,
OP.ProductID
From dbo.[Order] O
Inner Join (Select OrderId From OrderProduct where ProductID in(1,3) group by OrderId having COUNT(ProductID)=2) B On O.OrderId=B.OrderID
Inner Join OrderProduct oP on OP.OrderId=B.OrderID
Inner Join Product P on P.ProductID=OP.ProductID
Where P.ProductId in (1,3)

Cheers
MIK

Wow, it seems to work. I would have NEVER figured this one on my own. So I will need to pass in a comma-delimited row of numbers for the ProductIDs selected, right? Because I will definitely have bigger combinations of productIDs besides 1 & 3, more like 1, 3, 5, 6,8, 9, etc. And I would have to (before I run this Seleet statement), count how many I have and use that also as a parameter where you have count=2. Correct?

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2011-03-05 : 12:25:04

quote:
Originally posted by jamesallen74

quote:
Originally posted by pk_bohra

Try this:

SELECT
[Order].OrderID,
[Order].OrderName,
Product.ProductID
FROM [Order]
inner join OrderProduct on OrderProduct.OrderID = [Order].OrderID
INNER JOIN Product ON OrderProduct.ProductID = Product.ProductID
Inner join (Select OrderID,count(Distinct OrderID) As Cnt
FROM OrderProduct group by OrderID having count(*) > 1) as OP
on OrderProduct.OrderID = OP.OrderID

PK, where in this statement does it indicate to look for Product1 and Product3 in the OrderProduct table?

Oops, Missed the where condition..

SELECT
[Order].OrderID,
[Order].OrderName,
Product.ProductID
FROM [Order]
inner join OrderProduct on OrderProduct.OrderID = [Order].OrderID
INNER JOIN Product ON OrderProduct.ProductID = Product.ProductID
Inner join (Select OrderID,count(Distinct OrderID) As Cnt
FROM OrderProduct where ProductID in (1,3)
group by OrderID having count(*) > 1) as OP
on OrderProduct.OrderID = OP.OrderID

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2011-03-05 : 12:37:40

if you need to extend it for larger set e.g. product id should be 1,2,3,4 then change the count(columnName)=4. this is just to make sure that all of the desired products exists in an order. Just in case if there is repetition of the same product ID in One Order then use count(Distinct columnName)=4

Cheers
MIK

jcelko
Esteemed SQL Purist

547 Posts

Posted - 2011-03-05 : 14:16:07

>> Suppose I have an Order table, a Product Table, and an Order_Product table
(for the items selected for each unique order). <<

Please post real DDL. Learn to use ISO-11179 rules for the data element names, avoid needless dialect and use ISO-8601 temporal formats, codes and so forth. People cannot read your mind, so post your code and clear specs if you really want help.

We do not use singular names for tables; they are sets. We need plural or (better) collective names. We look for relationship names and do not follow the old hyphenated convention from network databases (in English, your “Order_Product” is really awful). Use industry standard data elements , “product_id” becomes “upc”, or “sku” or whatever your industry uses. There is also an idiom for orders and their details.

What you want is called a Relational Division; it was one of Codd's original operators.

How do I Select a list of Orders that have BOTH (not OR) Product1 and Product3 selected in their order? Here is what I have and it's not returning what I expect:

CREATE TABLE Orders -– unless you had only one, which is what you posted
(order_nbr INTEGER NOT NULL PRIMARY KEY,
..);

CREATE TABLE Order_Details
(order_nbr INTEGER NOT NULL
REFERENCES Orders(order_nbr)
upc CHAR(13) NOT NULL
REFERENCES Inventory (upc),
PRIMARY KEY (order_nbr, upc),
order_qty INTWEGEWR NOT NULL
CHECK (order_qtY > 0),
etc ..);

Now, let's create a market basket as the divisor:

CREATE TABLE Basket
(upc CHAR(13) NOT NULL PRIMARY KEY
REFERENCES Inventory (upc));

This is one version of an exact division. The order and the basket match exactly, no more no less.

SELECT OD1.order_nbr
FROM OrderDetails AS OD1
LEFT OUTER JOIN
Basket AS B1
ON O1.upc = B1.upc
GROUP BY O1.order_nbr
HAVING COUNT(O1.upc) = (SELECT COUNT(upc) FROM Basket)
AND COUNT(B1.upc) = (SELECT COUNT(upc) FROM Basket);

Play with the HAVING clause to get subsets, partial matching, etc.

--CELKO--
Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL

mmarovic
Aged Yak Warrior

518 Posts

Posted - 2011-03-06 : 06:23:43

You want to return one order row for orders containing two products, but you display just one productId. Assuming you actually wanted both IDs displayed probably the fastest executing solution follows. If you actually wanted to display just order ID and name then just remove product ID columns from the list.

SELECT 

O.OrderID, 
O.OrderName,
p1.ProductID as prd1,
p2.ProductID as prd2

FROM Order o
JOIN OrderProduct l1
ON O.OrderID = OP.OrderID 
JOIN Product p1
ON l1.ProductID = P1.ProductID
join orderProduct l2 on l2.orderID = o.orderID
join product p2 on l2.productID = p2.productID

Mirko

My blog: http://mirko-marovic-eng.blogspot.com/

jamesallen74
Starting Member

4 Posts

Posted - 2011-03-06 : 13:10:26

quote:
Originally posted by mmarovic

You want to return one order row for orders containing two products, but you display just one productId. Assuming you actually wanted both IDs displayed probably the fastest executing solution follows. If you actually wanted to display just order ID and name then just remove product ID columns from the list.
SELECT

O.OrderID,
O.OrderName,
p1.ProductID as prd1,
p2.ProductID as prd2

FROM Order o
JOIN OrderProduct l1
ON O.OrderID = OP.OrderID
JOIN Product p1
ON l1.ProductID = P1.ProductID
join orderProduct l2 on l2.orderID = o.orderID
join product p2 on l2.productID = p2.productID

Mirko

My blog: http://mirko-marovic-eng.blogspot.com/

Should "OP" be "l1" instead? Line 10? And does it matter which side of the "=" sign those 2 statements on line 10? I see in line 15, which is similar to line 10, they are reversed.



1  SELECT 
2
3  O.OrderID, 
4  O.OrderName,
5  P1.ProductID as prd1,
6  P2.ProductID as prd2
7  FROM Order O
8
9  JOIN OrderProduct L1
10  ON O.OrderID = L1.OrderID   --this is where "OP" was instead of "l1"
11  JOIN Product P1
12  ON L1.ProductID = P1.ProductID
13
14  JOIN orderProduct L2 
15  ON L2.orderID = O.orderID  --these are reversed from the example in line 10
16  JOIN product P2 
17  ON L2.productID = P2.productID

mmarovic
Aged Yak Warrior

518 Posts

Posted - 2011-03-15 : 09:16:04

Sorry for the late answer, I forgot to subscribe to the topic.

quote:
Should "OP" be "l1" instead? Line 10?

You can use as a table alias whatever you want. I had to join OrderProduct link table twice, so I opted to use l1 and l2 as aliases (as link 1, link2).

quote:
And does it matter which side of the "=" sign those 2 statements on line 10? I see in line 15, which is similar to line 10, they are reversed.

It does not matter for execution. It is just easier to follow if you have consistent order, which I do not have in that code.

Mirko

My blog: http://mirko-marovic-eng.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources