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`:
`Categories` has:
`id`, int auto-increment
`name` text
`parent` int id of parent or NULL which means that `name` is THE parent
An example:
id=0; name=Language; parent=NULL
id=1; name='PHP'; parent=0;
id=2; name='Javascript'; 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"
Like this:
0, "Using error_log in php", 1, "blah blah blah"
1, "Using echo in php", 1, "blah, blah, blah"
2, "popups in javascript", 2, "blah blah blah"
3, "Improper shutdown", 5, "annoying error everytime you start windows"
Category is one of the child categories from the `Categories` table (and can never be a parent category) so 1 referrs to PHP and 2 refers to javascript and 5 refers to 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
Javascript (1) - one article in the Javascript category
Or, if they selected the category 'Operating System' they would they would see one sub-category:
Windows (1)
OK, so how in the heck do I write this query? LOL