The Arcive of Official vBulletin Modifications Site.It is not a VB3 engine, just a parsed copy! |
|
#1
|
|||
|
|||
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. |
#2
|
||||
|
||||
looks like you are trying to code up competition for hivemail
Amy |
#3
|
|||
|
|||
My mail system isn't planning to compete with it.
Ideas? |
#4
|
|||
|
|||
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.
|
#5
|
|||
|
|||
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 |
#6
|
||||
|
||||
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] |
#7
|
|||
|
|||
No go, although my now-5-query workaround works.
|
|
|
X vBulletin 3.8.12 by vBS Debug Information | |
---|---|
|
|
More Information | |
Template Usage:
Phrase Groups Available:
|
Included Files:
Hooks Called:
|