ERROR MESSAGE on SQL QURRIES

[jhd67]

I am switching from access to MYSQL, but having a hard time with the Syntax on MYSQL. All though everything works fine in access, Please Help Thank you

SQL-query :

SELECT CATS.CAT_ID, CAT_NAME, CAT_IMAGE, (SELECT COUNT (*) FROM ADS WHERE ADS.CAT_ID = CATS.CAT_ID AND AD_APPROVED = 1 AND AD_EXPIRED > DATE() ) AS TOTAL FROM CATS WHERE cat_name like '” + Replace(rsCat__varcat_name, “'”, “''”) + “%' ORDER BY CAT_NAME ASC LIMIT 0, 30

MySQL said:

You have an error in your SQL syntax near 'SELECT COUNT (*) FROM ADS WHERE ADS.CAT_ID = CATS.CAT_ID AND AD_APPROVED = 1 A' at line 1

Next One:

___________________________________________________

SQL-query : [Edit]

SELECT *,(SELECT COUNT (*) FROM ADS WHERE ADS.Type_ID = Types.Type_ID AND AD_APPROVED = 1 AND AD_EXPIRED > DATE()) AS TOTAL FROM TYPES ORDER BY TYPE_NAME ASC LIMIT 0, 30

MySQL said:

You have an error in your SQL syntax near 'SELECT COUNT (*) FROM ADS WHERE ADS.Type_ID = Types.Type_ID AND AD_APPROVED = ' at line 1

[Ritesh]

Which version of MySQL you are using?

MySQL does not support sub-select which you are trying to use as of now. It is a planned feature for their future releases.

[jhd67]

Using the last one from mysql.com

what's my ulternative to writte this querries

Thank you

[CalEvans]

Currently, your only alternative is to re-write your queries to use left joins. This solution will allow you to work around most situations where you would use a sub select in the where clause. (That's where the majority of them exist.)

As for sub selects in the field list. I've never seen that done. I'm not entirely sure that's ANSI standard. MySQL is working towards ANSI93 compatibility. If it's not in the ANSI93 spec, it probably won't be in MySQL.

FWIW, the last version of MSSQL and Oracle I worked with did not have this 'feature'.

The other thing you can do is re-design your database so as not to require these. Not knowing your schema or problem space, I couldn't begin to suggest how. But you might want to take a step back. It might mean de-normalizing a bit.

HTH,

=C=

[Author]

Posts