I've never used that, but the manual says it's for fixing up tables after a lot of deletions. I don't see how that applies to this problem. Forgive my ignorance, please, if I've missed something.
Brian ------------------------------------ "How should I know if it works? That's what beta testers are for. I only coded it." - Linus Torvalds ----- Original Message ----- From: Simon Green <[EMAIL PROTECTED]> To: "Mysql (E-mail)" <[EMAIL PROTECTED]> Date: 12/20/01 2:30:43 AM > >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 --------------------------------------------------------------------- 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
