Using COUNT in subquery...

[imstillatwork]

Hello,

here's the deal...

SELECT
C1.categories_id,
C1.categories_name,
C1.parent_id,
C1.sort_order,
(SELECT COUNT(*) FROM categories AS C2 WHERE C2.parent_id=C1.categories_id) AS cat_count
FROM
Categories AS C1
WHERE
C1.parent_id=0
ORDER BY C1.sort_order


Should this work in Mysql? It will in access / mssql

Can't get it too.... what else should I do?

 

[swampBoogie]

Depends on which Mysql version you have. Mysql has support for scalar subqueries in 4.1 which is in alpha stage. It is not fit for production but you can use it for testing purposes.

 

[imstillatwork]

oh, I must have missed that, I'm on 4.0 since it came out a while back. Its all dev, so I'll have to play with that, by the time I need it, it should be ready for prod...I hope.

thats a pretty major feature to be missing, what do you do to get around it without bloating your code? ? ?

 

[swampBoogie]

what do you do to get around it without bloating your code?

Use another DBMS.

 

[r937]

Use another DBMS

cynic ;-)

what i would do is rewrite the query using a JOIN and a GROUP BY


rudy

http://r937.com/