Results 1 to 6 of 6
  1. #1
    Join Date
    Apr 2004
    Posts
    2

    Unanswered: select, count(), group by problem -please help with query

    I have 2 tables:

    1. ORDERS:

    order_id
    --------
    1
    2
    3


    2. ORDER_PRODUCTS

    order_product_id | order_id | status

    76---------------------1--------------ok
    77---------------------1--------------ok
    78---------------------1--------------ok
    80---------------------2--------------ok
    82---------------------2-------------xxx
    82---------------------2--------------ok
    83---------------------3--------------ok
    84---------------------3--------------ok

    What I need:

    I need to get all distinct orders from orders table with any status in
    order_products table, BUT don't take an order if it has status "ok" AND
    appears in order_products table exactly 3 times.

    Here is the same but in other words:

    I need to get all distinct orders from orders table,
    EXCEPT of those for which the number of order_products with
    status "ok" is equal to 3 AND for which there are no any other
    statuses rather then OK.

    Please help! I have no problems with a query that gives me exactly the orders I DON'T need:

    select distinct orders.*, count(order_products.order_product_id) from orders
    left join order_products on order_products.order_id = orders.order_id
    where
    order_products.status = 'OK'
    group by orders.order_id
    having count(order_products.order_product_id) = 3
    order by orders.order_id

    Thanks a million for help!
    P.S. I can't use sub-selects (don't have MySql 4.1)

  2. #2
    Join Date
    Jan 2003
    Location
    Paris, France
    Posts
    320
    Try something like


    select distinct orders.*, count(order_products.order_product_id) AS NbOrder from orders
    left join order_products on order_products.order_id = orders.order_id AND order_products.status = 'OK'

    group by orders.order_id
    having NbOrder <> 3
    order by orders.order_id
    Olivier Miossec
    --
    http://www.lasso-developpeur.net/
    --

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Olivier, this question is almost certainly someone's homework, and you do the questioner a disservice by not following sql standards

    any time you have a GROUP BY, it must include all non-aggregate columns in the SELECT list

    in this particular example, when you have orders.* in the SELECT list but only orders.order_id in the GROUP BY, you are fortunate, because the orders table appears to have only one column in it!

    in general you cannot use * in the SELECT list unless you itemize each and every column in the GROUP BY

    only mysql lets you get away with "hidden" group by fields (do a search on mysql.com for "group by hidden")

    finally, i just wanted to point out that DISTINCT with GROUP BY is unnecessary (and wasteful), since groups are, by definition, unique
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Jan 2003
    Location
    Paris, France
    Posts
    320
    Sorry just fast a cut and past from the previus query
    distinct is not usefull here
    Olivier Miossec
    --
    http://www.lasso-developpeur.net/
    --

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    "I need to get all distinct orders from orders table, EXCEPT of those for which the number of order_products with status "ok" is equal to 3 AND for which there are no any other statuses rather then OK."

    Code:
    select orders.order_id
         , orders.foo
         , orders.bar
         , count(order_products.order_id) 
      from orders 
    left 
      join order_products 
        on orders.order_id  = order_products.order_id 
    group 
        by orders.order_id 
         , orders.foo
         , orders.bar
    having count(order_products.order_id) <> 3 
        or (
           count(order_products.order_id) = 3 
       and count(distinct order_products.status) > 1
           )
        or (
           count(order_products.order_id) = 3 
       and count(distinct order_products.status) = 1
       and max(order_products.status) <> 'OK'
           )
    order 
        by orders.order_id
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Apr 2004
    Posts
    2
    Thanks to all! Rudy, I already mentioned in devshed forum (http://forums.devshed.com/t138173/s.html) it wasn't a homework, but just simplified, rephrased version of what I really need.

    I'm sure the last query your wrote is defenitely gonna work! Thank you so much! And I promse will never use * with group by!
    Thanks again!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •