Look at the other post you made in the "Coding" forum. If you don't want the total number you can remove the "count(news3.news_id) as all" and the "LEFT JOIN newsitems as news3 on (categories.cat_id=newsitems.cat_id) "
You cannot get all this info out with just one query unless you goin across the two tables. The reason it is made possible in the other example is that you have the categories table to join the newsitems table off multiple times with different criteria, note that this does not mean you have to select anything out of the categories table.
I do have to confess that I made a little mistake in writing out the query in the other reply. Whenever referencing newsitems outside of the left join (that includes in the left join condition) you must use the alias.
like so
SELECT categories.cat_id, count(news1.news_id) as published, count(news2.news_id) as unpublished
FROM categories
LEFT JOIN newsitems as news1 on (categories.cat_id=news1.cat_id && news1.published=1)
LEFT JOIN newsitems as news2 on (categories.cat_id=news2.cat_id && news2.published=0)
GROUP BY categories.cat_id
Now, I've done an explain on the query (changed around a little to fit my database) and the query itself is a little less efficient than doing a full SELECT (select with no where clause) on newsitems however (and a big however so pay attention 😉) I believe it will be quicker.
Firstly, a breakdown of the explains.
Doing a full SELECT on newsitems will be type=ALL (every row in the table must be looked at) using my query it will be type=ALL on categories (a smaller table than newsitems) but then type=ref twice (once for each left join) on the newsitems. I won't explain how we calculate this but the result it that my query will have to ultimately check more rows in the database than a full SELECT on newsitems.
Here comes the however; the resultset passed back to php (this is generally a bottlekneck and therefore should be reduced as much as possible) is vastly reduced as only three elements for each category are returned.There is also no need for any coding to handle the data once it has been recieved by php which, although not so labour intensive, is a bonus.
Overall however, I think the quickest way (I hate to say this and thus knock the wind out of my wonderfull query) to do this would be to run two queries each with a count in. The extra time taken in transfering data between php and MySQL and MySQL processing two individual queries will be vastly outweighed by the time you'll save in query efficiency and lack of php based processing.
HTH
Bubble
PS
I feel as though I have finnaly come of age in terms of databases. Either that or I've just spouted the most s**t ever 😃
