[Resolved] [Resolved] LEFT JOIN with COUNT over 3 tables?

FoTo50

As I have a table with kinda folders stored with a refer field to itself (similar to forum threads) I found it easy to create a SELECT to count all subfolders:

SELECT
folder1.*,
COUNT(folder2.nr) AS subfolder
FROM
folder AS folder1
LEFT JOIN
folder AS folder2
ON
folder2.refer = folder1.nr
WHERE
folder1.nr = '$id'
GROUP BY
folder1.nr
ORDER BY
folder1.orderfield;

That works pretty fine: in 'subfolder' I find the amount of rows that hafe referred to $id. Now I want to count some other's table rows that refer to this table as well:

SELECT
folder1.*,
COUNT(folder2.nr) AS subfolders,
COUNT(files.nr) AS files
FROM
folder AS folder1
LEFT JOIN folder AS folder2 ON folder1.nr = folder2.refer
LEFT JOIN files ON folder1.nr = files.folder
WHERE
folder1.nr = '$id'
GROUP BY
folder1.nr
ORDER BY
folder1.orderfield

Suddenly the 'subfolders' and 'files' are multiplied together 🙁

anyone has an idea what I do wrong with this (second) LEFT JOIN?

csn

You're obviously using mysql 😉, which allows sloppy group by syntax. Select only what columns you need, then group by on each of them.

FoTo50

Yes, sorry ... I forgot to mention that I use MySql 3.23.55 and unfortunately cannot upgrade to 4.1.0 where finally subselects would be supported.

Unfortunately the GROUP BY wont help. I tried:

SELECT
folder1.nr,
folder1.name,
COUNT(folder2.nr) AS subfolders,
COUNT(files.nr) AS files
FROM
folder AS folder1
LEFT JOIN folder AS folder2 ON folder1.nr = folder2.refer
LEFT JOIN files ON folder1.nr = files.folder
WHERE
folder1.nr = '4'
GROUP BY
folder1.nr,folder1.name,folder2.nr,files.nr
ORDER BY
folder1.reihe

...but this would only give a number of results from subfolders*files.

A funny thing I found out is:
When I GROUP BY folder2.nr only, I get as result in 'subfolders' AND in 'files' what I expect to be in 'files' only and in mysql_num_rows() what I expect to be in 'subfolders' ... when I GROUP BY files.nr only, mysql_num_rows() would return what I expect in 'files' ... and in 'subfolder' and 'files' is the value, that should be only in 'subfolders' ... 😕

As long as I get the proper values (wherever they might turn out ... lol) I might be able to continue but I hope there's somebody out there who can give me a hint, how a problem like this is supposed to be solved 😉

Greets
Georg

FoTo50

The above mentioned way works only as long as there isnt a zero count in 'subfolders' or 'files'. If so, it returns the values as expected ... hmmm ... That makes this way unusable for further needs. It seems that I find no way to solve this within one SELECT 🙁

FoTo50

FYI:

OUFF ... after a whole day searching all over the internet (but not giving up), I found the hints at the User Notes of the MySQL Manual itself ... grrr .. why I didnt look there from the beginning :mad:

The secret is in the combination of COUNT and DISTINCT:

SELECT
folder1.nr,
folder1.name,
COUNT(DISTINCT folder2.nr) AS subfolders,
COUNT(DISTINCT files.nr) as files
FROM
folder AS folder1 LEFT JOIN folder AS folder2 ON folder1.nr = folder2.refer,
folder LEFT JOIN files ON files.folder = folder1.nr
WHERE
folder1.nr = '$id'
GROUP BY
folder1.nr

works now perfect for counting results (on even more than 3 tables) with left joins 😃