[SabreWolf3]

Hi all,

I am learning SQL, and am working on a practice database here at work. The orders table in this database has two fields I am currently working with; Order ID and Shopper ID. For a given Order ID, there may be multiple records (multiple items ordered), but each record should have the same Shopper ID.

I was able to go through and force some incorrect Shopper ID's into some records for a few order ID's. Now I want to write a query that will locate each of these Order ID's and associated Shopper ID's.

I am unsure of how to proceed. I guess I have to evaluate every Order ID in the database, find the min(shopper_id) and max(shopper_id) and compare those two values. When they are the same, ignore this order ID. If the two values are not identical, I need to return this order_id and all shopper_id's associated with it.

Can someone help me finish my statement to do this? The statement below will return a list of all Order_ID's, and the Min and Max Shopper_ID for each Order_ID, on the date of 05/25/2004. Now, how do I get the SQL statement to compare the Min and Max values and only return records where they don't match?

SELECT Order_Date, Order_ID, min(Shopper_ID) as Min_ID, max(Shopper_ID) as Max_ID
FROM Order_Detail 
WHERE (Order_Date = {d '2004-05-25'})
GROUP BY Order_Date, Order_ID

[coderitr]

This is somewhat more advanced SQL than I typically use but have you tried

WHERE (Order_Date = {d  '2004-05-25'}
AND Min_ID <> Max_ID
GROUP BU Order_Date, Order_ID

[SabreWolf3]

Hi coderitr,

Thanks for your reply. I had already tried the statement you gave me before I posted my question on this forum. It makes perfect sense that this statement would work, but my application kept throwing me syntax errors.

I finally found a SQL expert at work this morning, and he answered my question for me. Here is the code that worked, although I'm not 100% able to explain why it must be written this way.

He didn't have time to fully explain, but he said that the fields in my sub-select statement needed to be fully qualified because of the comparison of the aggregate functions. Then, the WHERE statement needed to be changed to a WHEN statement.

That's a bit over my head, but all I know is that it works like a charm!

SELECT * FROM 
(SELECT 
Order_Date, 
Order_ID, 
min(Shopper_ID) as Min_ID, 
max(Shopper_ID) as Max_ID

FROM Order_Detail 

WHERE (Order_Date = {d '2004-05-25'})

GROUP BY Order_Date, 
Order_ID) as T1(Order_Date,Order_ID,Min_ID,Max_ID)
when Min_ID<>Max_ID

[coderitr]

Okay that makes some sense to me. A bit over my head though. I didn't even know that SQL had a "when" keyword. Glad its working for you. I would have probably written some code around it to filter out the rows I didn't want; i.e.; those where min_id = max_id; but if you can make the database do it for you then thats even better.

Cheers