Can OPTIMIZE help at all? Simon
-----Original Message----- From: Brian E. Lozier [mailto:[EMAIL PROTECTED]] Sent: 20 December 2001 10:14 To: [EMAIL PROTECTED] Subject: Complex Left Join I'm trying to accomplish something a bit complex with a single query. Basically, I'm doing a nested category/file listing, and I want to get the number of categories and the number of files under each category in the list. For example: http://www.massassi.org/saberworks/files.php user: kedri pass: irdek (sorry it's password protected for now) The problem is that my counts aren't coming out correctly. I can get either the category number OR the file number to be consistently correct, but not both. Here is the query I have so far: SELECT cat.*, COUNT(cat2.catID) AS numCats, COUNT(files.fileID) AS numFiles FROM fileCategories cat LEFT JOIN fileCategories cat2 ON cat.catID = cat2.catParent LEFT JOIN files ON files.catID = cat.catID WHERE cat.catParent = $catID GROUP BY cat.catName ORDER BY cat.catName The problem is that most of the time, the cat count is correct (but not always), and the files count seems to be off when there's more than one file. Any help would be greatly appreciated! I can get it easily with multiple queries, but I'd rather do it in one, if possible. Thanks in advance, Brian --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
