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);

SELECT `order_groups`.*

FROM `order_groups`

WHERE `order_groups`.`ship_notif

AND `order_groups`.`affiliate_

AND (

(

select count(*)

from orders

where order_group_id=order_group

and state != 'shipped'

and state != 'canceled'

) = 0

AND (

select count(*)

from orders

where order_group_id=order_group

and state = 'shipped'

)

> 0);

ASKER CERTIFIED SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

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
```

SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Yes, I missed that. Thanks PortletPaul for correcting that.

np. wish I could type seelct without having to correct it once or twice :)

ASKER