Query to find matching patterns in a table
I'm looking to construct a query that would pull matching groups of record from a table. Specifically, the table is a list of items ordered by customers in an e-commerce site. What I'm trying to find is all of the customers who ordered a specific group of products.
Here's a simplified version of my table structure:
OrderNumber int
CartItemID int
CustomerID int
ProductID int
Quantity int
Simply put, I want to pull a list of customers who ordered ProductID 31, Product ID 61 (twice) Product ID 59, Product ID 60, and Product ID 9999 all within the same order. It doesn't matter if they ordered items in addition to these.
Here's a simplified version of my table structure:
OrderNumber int
CartItemID int
CustomerID int
ProductID int
Quantity int
Simply put, I want to pull a list of customers who ordered ProductID 31, Product ID 61 (twice) Product ID 59, Product ID 60, and Product ID 9999 all within the same order. It doesn't matter if they ordered items in addition to these.
ASKER
No, what it means is that the ProductID 61 shows up twice in the order (two seperate shipping adddresses)
ASKER
Oh man, I always hated this error until I understood what it meant finally:
Column 'Order_Items.CartItemID' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Here's a bonus for you. Looking closer at the structure of the table... THERE IS NO PRIMARY KEY! Geniuses that designed this thing. *Sigh* Wasn't me, I swear! There is sort of a primary key in that OrderID/CartItemID *should* be unique.
Column 'Order_Items.CartItemID' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Here's a bonus for you. Looking closer at the structure of the table... THERE IS NO PRIMARY KEY! Geniuses that designed this thing. *Sigh* Wasn't me, I swear! There is sort of a primary key in that OrderID/CartItemID *should* be unique.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
That didn't work. It's not even pulling the one order that I know has those items. Here's the output from the order I'm trying to see if anybody else placed:
OrderNumber
252821101 318599 
252821 102 31859
252821 102 31859
252821 104 31859
252821 105 31859
252821 106 31859
ASKER
Not sure how that got submitted. I didn't hit enter...weird. Let's try formatting that a little better this time.
Anyways, I'm trying to find out if anybody else ordered these same products within the same order.
Your query brought up 4 items from one customer, but four seperate orders. THe ProductID was 59.
Anyways, I'm trying to find out if anybody else ordered these same products within the same order.
Your query brought up 4 items from one customer, but four seperate orders. THe ProductID was 59.
252821 101 318599 31 1
252821 102 318599 61 2
252821 102 318599 61 3
252821 104 318599 59 1
252821 105 318599 60 2
252821 106 318599 9999 1
try this, if you still did not get the answer...
this query will give you all such orders
this query will give you all such orders
SELECT *
FROM orders o,
( SELECT orderNumber
FROM orders
WHERE productid IN (31, 59, 60)
OR (productid = 61 AND Quantity = 2)
GROUP BY orderNumber
HAVING COUNT ( * ) = 4) x
WHERE o.ordernumber = x.ordernumber
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
@HainKurt: That at least pulled up the one order I know of like this (the one I'm trying to match).
I guess I shouldn't have mentioned my quantity column, because I'm not worried about that one at all. I'm just concerned about which products were ordered.
Why haven't either of you dealt with Product ID 9999?
I've looked at the two queries and 'distilled' them down to the following query and seem to have come up with the same result...just the single order I know about.
I guess I shouldn't have mentioned my quantity column, because I'm not worried about that one at all. I'm just concerned about which products were ordered.
Why haven't either of you dealt with Product ID 9999?
I've looked at the two queries and 'distilled' them down to the following query and seem to have come up with the same result...just the single order I know about.
SELECT CustomerID, OrderNumber
FROM Order_Items
WHERE ProductID IN (31,61,59,60,9999)
GROUP BY CustomerID, OrderNumber
HAVING COUNT(OrderNumber) = 6
ORDER BY CustomerID, OrderNumber
can you have two records in same order with productid=61???
if yes, whats the purpose of quantity?
if yes, whats the purpose of quantity?
Would it be possible for you to provide some sample set from your table and expected result?
ASKER
Ah, so in the end it doesn't matter, because my customer is a...well, I shouldn't say it. After looking at this for a while, I noticed that the order was from 2006! I asked her why she cared about such an old order. She said it was because it was an order she didn't get shipped. Well....I hate to tell you this, but go talk to your old website guy, because anything before 2008 came from your old site/database. I submitted a ticket for my boss's approval. Let him decide if he wants to charge this lady for 2 hours of my time because she's a...well again, I'm not gonna say it.
Thanks for the help folks.
Thanks for the help folks.
ASKER
@HainKurt - The reason two of the same product number could show up would be different 'Ship To' addresses.
ASKER
Thanks for the help...
SELECT *
FROM UrTable
WHERE CartItemID in (
SELECT CartItemID from yourTable
WHERE ProductID IN (31,61,59, 60)
GROUP BY ProductID
Having Count(*) = 4
)