Classic JOIN help
 

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?
[sql]
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
[/sql]
If you want I can give a schema for both tables but you shouldn't need it, I think.

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

Amy

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

Ideas?

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. :)

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

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

try this:

[sql]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[/sql]

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