Posted by Hilarion on 10/14/53 11:36

> I have a problem to migrate a software to MySQL 4 the request below
> works perfectly with MySQL 3 and not with MySQL 4. If somebody jave
> any idea.

[ snip ]

> SELECT c.coupon_id, c.coupon_code, c.coupon_amount, c.coupon_type,
> c.coupon_start_date, c.coupon_expire_date, c.coupon_minimum_order,
> c.coupon_minimum_total_orders, c.uses_per_user, c.uses_per_coupon,
> c.restrict_to_products, c.restrict_to_categories,
> c.restrict_to_customers, c.restrict_to_existings_customers,
> date_created, date_modified, (
>
> SELECT count( * )
> FROM coupon_email_track AS et
> WHERE c.coupon_id = et.coupon_id
> ) AS email_count
> FROM coupons c
> WHERE c.coupon_active = 'Y' AND c.coupon_type = 'G'
> ORDER BY c.date_created DESC


You could use LEFT OUTER JOIN and GROUP BY instead of nested SELECT.
Something like:


SELECT
c.coupon_id, c.coupon_code, c.coupon_amount, c.coupon_type,
c.coupon_start_date, c.coupon_expire_date, c.coupon_minimum_order,
c.coupon_minimum_total_orders, c.uses_per_user, c.uses_per_coupon,
c.restrict_to_products, c.restrict_to_categories,
c.restrict_to_customers, c.restrict_to_existings_customers,
c.date_created, c.date_modified,
COUNT( et.coupon_id ) AS email_count
FROM
coupons AS c LEFT OUTER JOIN
coupon_email_track AS et ON c.coupon_id =
WHERE
c.coupon_active = 'Y' AND
c.coupon_type = 'G'
GROUP BY
c.coupon_id, c.coupon_code, c.coupon_amount, c.coupon_type,
c.coupon_start_date, c.coupon_expire_date, c.coupon_minimum_order,
c.coupon_minimum_total_orders, c.uses_per_user, c.uses_per_coupon,
c.restrict_to_products, c.restrict_to_categories,
c.restrict_to_customers, c.restrict_to_existings_customers,
c.date_created, c.date_modified
ORDER BY
c.date_created DESC


Hilarion

[Back to original message]