6
\$\begingroup\$

So, I have two tables, customers and orders and I want to get all orders associated with a customer.

The tables look like this:

Customers

customer_no   order_no
       1027       V327
       1027       V391
       1028       V400
etc..

Orders

order_no article1 article2 size order_date quantity
    V327    Jeans    Black   36   20150101        2
    V327    Shirt     Blue    L   20150101        1
    V327    Shirt    White    L   20150101        2
    V391    Jeans      Red   34   20150202        3
    V400    Shirt    Green   32   20150226        2
etc..

Now, I currently use the following query, but it is dog slow and I just know I'm approaching this wrongly. I feel like it should be possible to solve this with a join somewhere, but for the life of mine, I just cannot figure this one out.

SELECT article1, article2, size, order_date, SUM(quantity)
FROM orders
WHERE order_no IN (
    SELECT order_no
    FROM customers
    WHERE customer_no = '1027'
)
GROUP BY article1, article2, size, order_date, SUM(quantity)

The output for customer 1027 is (and should be):

article1 article2 size order_date SUM(quantity)
   Jeans    Black   36   20150101             2
   Shirt     Blue    L   20150101             1
   Shirt    White    L   20150101             2
   Jeans      Red   34   20150202             3

Does anyone have a better way of solving this? Performance is my main issue here.

\$\endgroup\$
  • 5
    \$\begingroup\$ Can you change the data model to normalize it? I'm wondering why order_no appears in the customers table. \$\endgroup\$ – πάντα ῥεῖ Dec 22 '15 at 14:18
  • 1
    \$\begingroup\$ @πάνταῥεῖ I only inherited this structure, and I really really do not want to break everything built on top of this database, so no, sadly. \$\endgroup\$ – N. Wouda Dec 22 '15 at 14:22
  • \$\begingroup\$ ...I dunno if this is just some quirk of MySQL, but GROUP BY an aggregate function (SUM(quantity)) looks extremely wrong to my eye. \$\endgroup\$ – jpmc26 Dec 22 '15 at 19:44
  • \$\begingroup\$ @jpmc26 that should've been size rather than SUM(quantity). I have no idea what brainfart of mine caused me to write that in the GROUP BY statement. \$\endgroup\$ – N. Wouda Dec 22 '15 at 19:46
  • \$\begingroup\$ You have size in the list, though. If it's not supposed to be there, then it looks to me like you just copied your output column list and forgot to delete it off the end. No harm. Just glad we got it cleared up. \$\endgroup\$ – jpmc26 Dec 22 '15 at 19:47
5
\$\begingroup\$

Applying a JOIN sounds pretty easy

SELECT article1, article2, size, order_date, SUM(quantity)
FROM orders 
INNER JOIN customers ON orders.order_no = customers.order_no
WHERE customers.customer_no = '1027'
GROUP BY article1, article2, size, order_date, SUM(quantity)

You should note that nested SELECT statements in a WHERE clause almost always lead to bad performance.


Also that kind of unnormalized data model looks weird, but there may be good reasons why to keep order_no and customer_no pairs in a single association table. May be its just bad naming, something like customers_orders might probably have been a better choice.

\$\endgroup\$
  • \$\begingroup\$ Thanks! This works beautifully. With regard to the data model, that was my horrible attempt to simplify and translate some Dutch to English, in Dutch it's klant_bestellingen, so pretty much customer orders. Please forgive me for my incompetent translation :). \$\endgroup\$ – N. Wouda Dec 22 '15 at 14:52
  • \$\begingroup\$ @N.Wouda De nada. Considering my SQL fu is pretty rusty nowadays, I was wondering if my proposal would work for you at all ;-). \$\endgroup\$ – πάντα ῥεῖ Dec 22 '15 at 14:55
3
\$\begingroup\$

I would assume that an INNER JOIN would be better:

SELECT customers.article1
    , customers.article2
    , customers.size
    , customers.order_date
    , SUM(customers.quantity)
FROM orders
INNER JOIN customers
    ON customers.order_no = orders.order_no
WHERE customers.customer_no = '1027'
GROUP BY customers.article1
    , customers.article2
    , customers.size
    , customers.order_date
    , SUM(customers.quantity)

Also, the SUM(customers.quantity) in the GROUP BY looks really odd.

\$\endgroup\$
0
\$\begingroup\$

Using an INNER JOIN would solve your issue:

SELECT 
 orders.article1, 
 orders.article2,
 orders.size, 
 orders.order_date, 
 SUM(orders.quantity) AS [Quantity]
FROM 
 orders INNER JOIN customers ON orders.order_no = customers.order_no
WHERE 
 customer.cust_no = 1027
GROUP BY 
 orders.article1, 
 orders.article2, 
 orders.size, 
 orders.order_date

The difference is that I removed the SUM(orders.quantity) from the GROUP BY since it doesn't belong in that statement. I corrected @BCdotWEB since he used the customers table and not the orders table when calling columns. Also, I am not sure how the voted answer worked as there should have been an error due to the SUM(orders.quantity) being used in the Group By.

\$\endgroup\$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.