Tough one
-
I can't figure out the following: I have order header and detail tables with UPC codes and the order numbers they belong to. There can be many different UPC codes for each order. The Order IDs are in the header table and the UPC codes are in the detail table. I must find UPC codes that have been used for more than one order, in other words, where the same UPC code exists for two or more distinct orders. I have tried:
SELECT OD.UPC, OH.OrderId, COUNT(DISTINCT OH.OrderId) [COUNT]
FROM OrderDetail OD
LEFT JOIN OrderHeader OH ON OH.OrderHeaderId = OD.OrderHeaderId
GROUP BY OD.UPC, OH.OrderId
HAVING COUNT(DISTINCT OH.OrderId) > 1But this query returns nothing and I'm not sure if it's because there are no duplicate orders or because the query is wrong. Any help will be tremendously appreciated. Thank you.
The difficult we do right away... ...the impossible takes slightly longer.
-
I can't figure out the following: I have order header and detail tables with UPC codes and the order numbers they belong to. There can be many different UPC codes for each order. The Order IDs are in the header table and the UPC codes are in the detail table. I must find UPC codes that have been used for more than one order, in other words, where the same UPC code exists for two or more distinct orders. I have tried:
SELECT OD.UPC, OH.OrderId, COUNT(DISTINCT OH.OrderId) [COUNT]
FROM OrderDetail OD
LEFT JOIN OrderHeader OH ON OH.OrderHeaderId = OD.OrderHeaderId
GROUP BY OD.UPC, OH.OrderId
HAVING COUNT(DISTINCT OH.OrderId) > 1But this query returns nothing and I'm not sure if it's because there are no duplicate orders or because the query is wrong. Any help will be tremendously appreciated. Thank you.
The difficult we do right away... ...the impossible takes slightly longer.
How about:
SELECT UPC, COUNT(DISTINCT OrderHeaderId)
FROM OrderDetail
GROUP BY UPC
HAVING COUNT(DISTINCT OrderHeaderId) > 1
"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer
"These people looked deep within my soul and assigned me a number based on the order in which I joined" - Homer
-
How about:
SELECT UPC, COUNT(DISTINCT OrderHeaderId)
FROM OrderDetail
GROUP BY UPC
HAVING COUNT(DISTINCT OrderHeaderId) > 1
"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer
Brilliant! I guess I was overthinking it. Thanks, Richard. You saved the last few hairs I have left.
The difficult we do right away... ...the impossible takes slightly longer.
