Classic JOIN help

[filburt1]

I have two tables: mailmessage and mailfolder. This query works perfectly (returns the number of messages in each folder) except for when one folder doesn't have any messages.

I know I have to use a JOIN to solve this. How do I do it?

SELECT mf.mailfolderid, mf.title, COUNT(mm.mailmessageid) AS messagecount
FROM mailmessage mm, mailfolder mf
WHERE mf.mailfolderid = mm.mailfolderid
AND mf.userid = 1 AND mm.userid = 1
GROUP BY mf.mailfolderid

If you want I can give a schema for both tables but you shouldn't need it, I think.

[amykhar]

looks like you are trying to code up competition for hivemail :D

Amy

[filburt1]

My mail system isn't planning to compete with it.

Ideas?

[filburt1]

I think I just coded a 53-line workaround that fetches all possible indicies, the ones the user has, and then diffs them. If it works? That's the question. :)

[okrogius]

try this:

SELECT mf.mailfolderid, mf.title, COUNT(mm.mailmessageid) AS messagecount
FROM mailfolder mf
LEFT JOIN mailmessage mm ON mailfolderid
WHERE mf.userid = 1 AND mm.userid = 1
GROUP BY mf.mailfolderid

[Xenon]

hmm, i think it would make problems, if it's empty and you are also asking for userids ....

try this:

SELECT mf.mailfolderid, mf.title, COUNT(mm.mailmessageid) AS messagecount
FROM mailfolder mf
LEFT JOIN mailmessage mm ON (mf.mailfolderid = mm.mailfolderid AND mm.userid = 1)
WHERE mf.userid = 1
GROUP BY mf.mailfolderid

[filburt1]

No go, although my now-5-query workaround works.