Link to home
Start Free TrialLog in
Avatar of c0mrad
c0mrad

asked on

MySQL Sub-Query

I am trying to re-write this query without the subquery. It has been a while since I had to rewrite a query and was wondering if anyone may be able to think of a better way to write this:

SELECT `order_groups`.*
FROM `order_groups`  
WHERE `order_groups`.`ship_notified` = 0
AND `order_groups`.`affiliate_id` = 51
AND (
            (
                  select count(*)
                  from orders
                  where order_group_id=order_groups.id
                  and state != 'shipped'
                  and state != 'canceled'
            ) = 0
      AND (
                  select count(*)
                  from orders
                  where order_group_id=order_groups.id
                  and state = 'shipped'
            )
> 0);
ASKER CERTIFIED SOLUTION
Avatar of Sharath S
Sharath S
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of c0mrad
c0mrad

ASKER

Tried to run, but I get a bunch of errors.
What are the errors?
check this
SELECT `order_groups`.* 
  FROM `order_groups` 
       INNER JOIN (SELECT SUM(CASE 
                          WHEN state NOT IN ( 'shipped', 'canceled' ) THEN 1 
                          ELSE 0 
                        end) AS Cnt1, 
                    SUM(CASE 
                          WHEN state IN ( 'shipped' ) THEN 1 
                          ELSE 0 
                        end) AS Cnt2 
               FROM orders) AS t1 
         ON t1.order_group_id = order_groups.id 
 WHERE `order_groups`.`ship_notified` = 0 
   AND `order_groups`.`affiliate_id` = 51 
   AND t1.Cnt1 = 0 
   AND t1.Cnt2 > 0 

Open in new window

SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Yes, I missed that. Thanks PortletPaul for correcting that.
np. wish I could type seelct without having to correct it once or twice :)