Hey All... This query is waaaay outside my skill set so I can't even fathom how to do this other than the "brut force" method and that's way to slow for my purpose (a mobile app).
I have two tables, `Categories` and `Articles`:
`id`, int auto-increment
`parent` int id of parent or NULL which means that `name` is THE parent
id=0; name=Language; parent=NULL
id=1; name='PHP'; parent=0;
id=4; name='Operating System'; parent=NULL
id=5; name='Windows'; parent=4
id=6; name='Linux'; parent=4
Records 0 and 4 are Parents while records 1 & 2 are children of record 0 and records 5 & 6 are children of record 4.
Now, in the next table `Articles` I have:
`id int "auto-increment"
`title` "title of the article"
`category` int "category from the above table, only a child category"
`text` "text of the article"
0, "Using error_log in php", 1, "blah blah blah"
1, "Using echo in php", 1, "blah, blah, blah"
3, "Improper shutdown", 5, "annoying error everytime you start windows"
When I get a query from a mobile device I need to return ALL of the categories AND each parent category will have the total articles in all its child/sub-catagories AND each child category will have the total of all the articles in that category.
So, on a mobile device you'd get a list of parent categories and then a number indicating the total number of articles in all of that parents children, like this:
Language (3) - there are three total articles in the Language category
Operating System (1)
When the user selects the category 'Language', they would see two sub-categories:
PHP (2) - two articles in the php category
Or, if they selected the category 'Operating System' they would they would see one sub-category:
OK, so how in the heck do I write this query? LOL