Tengo 3 tablas, cada una con la siguiente estructura
CREATE TABLE `categories` ( `idcategorie` int(11) NOT NULL AUTO_INCREMENT, `iduser` int(11) DEFAULT NULL, `categorie` varchar(45) COLLATE utf8_spanish2_ci DEFAULT NULL, PRIMARY KEY (`idcategorie`), KEY `iduser_idx` (`iduser`) ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8 COLLATE=utf8_spanish2_ci COMMENT='categorias principales. es padre de subcategories.'
CREATE TABLE `subcategories` ( `idsubcategorie` int(11) NOT NULL AUTO_INCREMENT, `idcategorie` int(11) DEFAULT NULL, `subcategorie` varchar(45) COLLATE utf8_spanish2_ci DEFAULT NULL, PRIMARY KEY (`idsubcategorie`), KEY `fk_idcategorie_idx` (`idcategorie`), CONSTRAINT `fk_idcategories` FOREIGN KEY (`idcategorie`) REFERENCES `categories` (`idcategorie`) ON DELETE NO ACTION ON UPDATE NO ACTION ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 COLLATE=utf8_spanish2_ci COMMENT='sirve para clasificar los posts. subcategories tiene clave foranea de la clave primaria de posts'
CREATE TABLE `posts` ( `idpost` int(11) NOT NULL AUTO_INCREMENT, `author` varchar(45) COLLATE utf8_spanish2_ci DEFAULT NULL, `date_time` datetime DEFAULT NULL, `title` varchar(45) COLLATE utf8_spanish2_ci DEFAULT NULL, `post` mediumtext COLLATE utf8_spanish2_ci, `conclusion` mediumtext COLLATE utf8_spanish2_ci, `footer` varchar(45) COLLATE utf8_spanish2_ci DEFAULT NULL, `idsubcategorie` int(11) DEFAULT NULL, PRIMARY KEY (`idpost`), FULLTEXT KEY `post_index` (`post`) ) ENGINE=MyISAM AUTO_INCREMENT=7 DEFAULT CHARSET=utf8 COLLATE=utf8_spanish2_ci
El objetivo de la consulta es obtener todas las entradas de categories, todas las entradas de subcategories relacionadas con cada entrada de categories, un count de todas las entradas de subcategories y otro count de todas las entradas de posts.
Esta es la consulta que he preparado
use miblog; SELECT C.*, S.*, COUNT(S.idsubcategorie), COUNT(P.idpost) FROM categories C, subcategories S, posts P group by S.idsubcategorie
Este es el resultado
# idcategorie, iduser, categorie, idsubcategorie, idcategorie, subcategorie, COUNT(S.idsubcategorie), COUNT(P.idpost)
1 1 Programación 1 1 PHP 4 4
1 1 Programación 2 1 MYSQL 4 4
1 1 Programación 3 2 En convivencia 4 4
El problema es evidente. Si bien obtengo todos los resultados agrupados por subcategories, los resultados de COUNT() no son los que tienen que ser, porque en realidad solamente hay dos entradas en posts y 3 entradas en subcategories (hay una subcategorie que no tiene posts asignado todavía).
No sé cómo poder hacer que COUNT() devuelva el número real de entradas de posts y subcategories, tampoco sé exactamente por qué COUNT devuelve 4 totales para subcategories y 4 totales para posts
¿Alguien puede ayudarme?