using multiples queries

[moonfish07]

SELECT *, (SELECT COUNT(*) FROM PHOTOPICTURES a,PHOTOCATEGORIES b WHERE a.PIC_CAT = b.CAT_ID) AS PIC_COUNT FROM PHOTOCATEGORIES ORDER BY CAT_NAME
can any1 tell me what's wrong with the above query as

SELECT *, (1) AS PIC_COUNT FROM PHOTOCATEGORIES ORDER BY CAT_NAME <----this gives a result

 

[sleipnir214]

It's probably the secondary select in your query. MySQL only supports subselects in a limited set of circumstances.

For more information, read here:

http://www.mysql.com/doc/en/ANSI_diff_Sub-selects.html

______________________________________________________________________

http://www.tuxedo.org/~esr/faqs/smart-questions.html

TANSTAAFL!

 

[moonfish07]

thanks for replying....
do u know of any method to make it into a single query

 

[Bromrrrrr]

Assuming from your query that you're trying to get the number of pictures for each category, this is what I would try:

SELECT c.*, COUNT (p.PIC_ID) AS PIC_COUNT
FROM PHOTOCATEGORIES c
LEFT JOIN PHOTOPICTURES p ON p.PIC_CAT = c.CAT_ID
GROUP BY c.CAT_ID

Hope this helps,

Brommrrrr