Nested SELECT in SELECT clause works in MySQL 3 and not in MySQL 4

Post removed (X-No-Archive: yes)

Re: Nested SELECT in SELECT clause works in MySQL 3 and not in MySQL4

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Cook wrote:
> Hello,
>...

Are you sure this query works with MySQL 3....? Subselects are firstly supported
in MySQL 4.1?!

Regards
Stefan
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.1rc2 (MingW32)

iD8DBQFDvoshyeCLzp/JKjARAn/BAJ4sHfTzKEJ8eF8kvBRkmE3brobLPgCf R/sV
98BUGZTGQ4IZ45j655P1Ewo=
=CInS
-----END PGP SIGNATURE-----

Re: Nested SELECT in SELECT clause works in MySQL 3 and not in MySQL 4

> 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

Re: Nested SELECT in SELECT clause works in MySQL 3 and not in MySQL 4

Post removed (X-No-Archive: yes)

Re: Nested SELECT in SELECT clause works in MySQL 3 and not in MySQL 4

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

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

> OK, thank you for your help but it's to complex for me (the original
> request have 4 nested select)

You could post that select here - maybe we can help to make it
simple.


> I write a little piece of PHP code ...

This may make it much slower.


HIlarion