Complex left join help

Anon

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

Anon

SELECT cat.*, COUNT(cat2.catID) AS numCats, COUNT(files.fileID) AS numFiles
FROM 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

There is no need to pull from filecategories if you doing a left join on it. Try this now. As now the query is limited to the left join.

Anon

Hrm, that code doesn't work at all. There's nothing on the 'FROM' line, because 'cat' isn't a table, it's an alias to 'fileCategories' - I was selecting from fileCategories twice (cat, cat2). Any other ideas?

[deleted]

"I can get it easily with multiple queries, but I'd rather do it in one, if possible."

You'll may have to.
First of all, you cannot select columns that are not in the GROUP BY clause (MySQL allows it, but MySQL does not care about data consistency)

Second, count(column) will count all records where column is not null, in this case you are joining the table on the IDs, and you are also counting the IDs, so you will always get the same count for both. (the JOIN will not give a result if any of the IDs is NULL)

You may be able to get a better result using CASE WHEN statements, but two queries is probable simpler for the timebeing.

Anon

Okay, I can get it if I run a query for each returned result from:

select * from fileCategories where catParent = $catID

Then I run a query for counting the categories and files, for each returned row from the above.

That's an absolute ton of queries if the page has a lot of files/categories (the current system is listing thousands of files and quite a few categories).

Any ideas on how to do it with just one or two queries? The problem is I need the results from the first query to feed into the subsequent queries... ah it would be nice to have subselect in this case.

[deleted]

I wouldn't go for that solution, try putting the results from the first query into a temporary table ans see if you can JOIN those results in a new query to get the rest of the results.

Anon

I've never used temporary tables. And the search on the mysql docs is absolutely horrendous 🙁 Could you point me to the page that talks about them please? The only one I could find was this that just talks about the problems with temporary tables, not what they are or how to use them.

http://www.mysql.com/doc/T/e/Temporary_table_problems.html

Thanks for all your help.

[deleted]

It's 'hidden' in the create table syntax
http://www.mysql.com/doc/C/R/CREATE_TABLE.html