I have 3 tables: "PageCat" for category data, "PageDoc" for Document data and "PageImg" for Image data. and I'd like to be able to list category names as well as the number of docs and imgs in each category.
However I get incorrect results, ie: If I add another file (Img or doc) the count for both imgs and docs increases, when only the doc count should increase.
The Table Structure:
* "PageCat" has a primary key auto_increment ID column ("PageCatID")
* "PageDoc" has a primary key auto_increment ID column ("PageDocID") and a foreign key for joins to the "PageCat" table ("PageDocPageCatID")
* "PageImg" also has a primary key auto_increment ID column ("PageImgID") and a foreign key for joins to the "PageCat" table ("PageImgPageCatID")
$sql = "SELECT PageCat.*,COUNT(PageDocPageCatID) AS DocCount,COUNT(PageImgPageCatID) AS ImgCount
LEFT JOIN PageDoc ON PageDocPageCatID=PageCatID
LEFT JOIN PageImg ON PageImgPageCatID=PageCatID
GROUP BY PageCatID
ORDER BY PageCatName";
I'm using PHP4.3.1/MySQL4.0.12-nt with Apache 1.3 on Win2k Prof.
Thanks for any pointers you can give me :)