[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 271
  • Last Modified:

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.
0
CCongdon
Asked:
CCongdon
  • 8
  • 3
  • 2
  • +1
2 Solutions
 
Aneesh RetnakaranDatabase AdministratorCommented:
-- not sure what do u mean by this "Product ID 61 (twice) "  is that qty for that product is 2

SELECT *
FROM UrTable
WHERE CartItemID in (
SELECT CartItemID from yourTable
WHERE ProductID IN (31,61,59, 60)
GROUP BY ProductID
Having Count(*) = 4
)
0
 
CCongdonAuthor Commented:
No, what it means is that the ProductID 61 shows up twice in the order (two seperate shipping adddresses)
0
 
CCongdonAuthor Commented:
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.
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
Aneesh RetnakaranDatabase AdministratorCommented:
sorry


SELECT *
FROM UrTable
WHERE CartItemID in (
SELECT CartItemID from yourTable
WHERE ProductID IN (31,61,59, 60)
GROUP BY CartItemID
Having Count(*) = 4
)
0
 
CCongdonAuthor Commented:

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 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 
0
 
CCongdonAuthor Commented:
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.

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

Open in new window

0
 
HainKurtSr. System AnalystCommented:
try this, if you still did not get the answer...

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

Open in new window

0
 
HainKurtSr. System AnalystCommented:
this is the list of such customers (assuming you have customers table)


SELECT   *
  FROM   customers c,
         (  SELECT   DISTINCT customerid
              FROM   orders
             WHERE   productid IN (31, 59, 60)
                     OR (productid = 61 AND Quantity = 2)
          GROUP BY   orderNumber
            HAVING   COUNT ( * ) = 4) x
 WHERE   c.customerid = x.customerid

Open in new window

0
 
CCongdonAuthor Commented:
@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.

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

Open in new window

0
 
HainKurtSr. System AnalystCommented:
can you have two records in same order with productid=61???
if yes, whats the purpose of quantity?
0
 
SharathData EngineerCommented:
Would it be possible for you to provide some sample set from your table and expected result?
0
 
CCongdonAuthor Commented:
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.
0
 
CCongdonAuthor Commented:
@HainKurt - The reason two of the same product number could show up would be different 'Ship To' addresses.
0
 
CCongdonAuthor Commented:
Thanks for the help...
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 8
  • 3
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now