need help with confusing left join

[quanghoc]

OK, I have 2 tables with fields:
+message: fromuserid, topicid, msgid, content
+ratelog: userid, raterid, msgid, pollid
Now I want to get all the record with topicid=31 in message table.
In addition, I want to look in the ratelog table to see if that record matches these conditions:
"ratelog.userid=message.fromuserid
AND ratelog.raterid=1
AND ratelog.msgid=message.msgid"
if not, ratelog.rate in return will be NULL
This is what I did and it is wrong:
select message.*, ratelog.rate from message left join ratelog
on ratelog.userid=message.fromuserid AND ratelog.raterid=1 AND ratelog.msgid=message.msgid
where topicid=31;
The wrong result was that it would return twice for a same record in message table sometime.
Anyone figure out this complicated problem (well to me), please help. Thank you.

[tomhath]

Have you checked the ratelog and message tables to be sure you don't have extra rows? The query looks okay, I'd suspect bad data.

[quanghoc]

Thank you for quick reply.
I'm sure I have the right data. Look
[FONT=courier new]
'message':
fromuserid | topicid | msgid | content
2 31 13 <MEMO>
1 31 14 <MEMO>
'ratelog':
userid | raterid | msgid | pollid | rate
2 1 13 0 2
2 1 0 22 3
[/FONT]
I want the result like this:
fromuserid | topicid | msgid | content | rate
2 31 13 <MEMO> 2
1 31 14 <MEMO> NULL
But it actually came out like:
fromuserid | topicid | msgid | content | rate
2 31 13 <MEMO> 2
2 31 13 <MEMO> 3
1 31 14 <MEMO> NULL
(there u see the double record which msgid=13 twice)
That's the problem.

[tomhath]

Are you running the query directly (in phpmyadmin or from the mysql command line)? Or from inside a php script?

It looks like the "AND ratelog.msgid=message.msgid " part isn't part of the query that was run.

[quanghoc]

it's in php

[tomhath]

The query you posted should give the results you want. It has to be either data or the query being run is not what you expect.

[quanghoc]

Oh, yeah, this is really supprise me. But my real tables aren't like that. There are more stuffs but it should be the same as I assume:
CREATE TABLE message (
msgid mediumint(3) unsigned NOT NULL auto_increment,
datetime varchar(10) NOT NULL default '',
fromuserid mediumint(3) unsigned NOT NULL default '0',
touserid mediumint(3) unsigned NOT NULL default '0',
content mediumtext NOT NULL,
topicid mediumint(3) unsigned NOT NULL default '0',
attachids varchar(50) default NULL,
fromusername varchar(50) default NULL,
tousername varchar(50) default NULL,
toreplyid mediumint(3) unsigned NOT NULL default '0',
replyid mediumint(3) unsigned NOT NULL default '1',
editlog varchar(16) default NULL,
msgrate tinyint(1) unsigned NOT NULL default '0',
enablehtml enum('y','n') NOT NULL default 'n',
signature enum('y','n') NOT NULL default 'y',
notify enum('y','n') NOT NULL default 'n',
showsmilies enum('y','n') NOT NULL default 'y',
parselink enum('y','n') NOT NULL default 'y',
PRIMARY KEY (msgid)
) TYPE=MyISAM;

#

Dumping data for table 'message'

#

INSERT INTO message VALUES("13", "1047778690", "2", "0", "test", "31", "5|||6", "", "{toguestusername}", "1", "2", "1|||1047778690", "0", "n", "n", "n", "y", "y");
INSERT INTO message VALUES("14", "1047785611", "1", "0", "test", "31", "8", "", "{toguestusername}", "2", "3", "1|||1047785611", "0", "n", "n", "n", "y", "y");

#

Table structure for table 'ratelog'

#

CREATE TABLE ratelog (
userid mediumint(3) unsigned NOT NULL default '0',
raterid tinyint(1) unsigned NOT NULL default '0',
msgid mediumint(3) unsigned NOT NULL default '0',
pollid mediumint(3) unsigned NOT NULL default '0',
rate tinyint(1) unsigned NOT NULL default '0',
datetime varchar(10) NOT NULL default '',
KEY msgpoll (msgid,pollid),
KEY raterid (raterid),
KEY userid (userid),
KEY userrater (raterid,userid),
KEY msgid (msgid),
KEY pollid (pollid)
) TYPE=MyISAM;

#

Dumping data for table 'ratelog'

#

INSERT INTO ratelog VALUES("2", "1", "13", "0", "2", "");
INSERT INTO ratelog VALUES("2", "1", "13", "22", "3", "");

SQL:
select message.*, ratelog.rate from message left join ratelog
on ratelog.userid=message.fromuserid AND ratelog.raterid=1 AND ratelog.msgid=message.msgid
where topicid=31;
The above sql statement return 3 records which two of them has msgid=13. What's wrong?

[tomhath]

Try running this query:

SELECT message.fromuserid, message.msgid, ratelog.userid, ratelog.raterid FROM message LEFT JOIN ratelog ON ratelog.userid = message.fromuserid AND ratelog.raterid = 1 AND ratelog.msgid = message.msgid WHERE topicid = 31

You'll see that you don't have any row in ratelog for userid 1 or messageid 14, but you have two ratelog entries from userid 2 for msgid 13. That's what your query is returning. It looks like the second insert into ratelog isn't what you intended.

fromuserid  msgid  userid  raterid  

2            13       2         1 
2            13       2         1 
1            14       NULL   NULL 

[quanghoc]

Originally posted by tomhath

You'll see that you don't have any row in ratelog for userid 1 or messageid 14, but you have two ratelog entries from userid 2 for msgid 13. That's what your query is returning. It looks like the second insert into ratelog isn't what you intended.

It is not neccessary to have record in ratelog for a userid or msgid in message table. That's the hard part. Now , from that data, all I want to return is a whole record with msgid=13 and field rate=2, and a whole record with msgid=14 and field rate=NULL.
Thanks for your reply.

[webm4st0r]

SELECT message.fromuserid, message.msgid, ratelog.userid, ratelog.raterid FROM message LEFT JOIN ratelog ON ratelog.userid = message.fromuserid AND ratelog.raterid = 1 AND ratelog.msgid = message.msgid WHERE topicid = 31 GROUP BY ratelog.msgid

try that

[quanghoc]

Thanks for that but it doesn't seem to work if I have more data.
This is for message table:

INSERT INTO message VALUES("15", "", "1", "0", "", "31", NULL, NULL, NULL, "0", "1", NULL, "0", "n", "y", "n", "y", "y");
INSERT INTO message VALUES("13", "1047778690", "2", "0", "test", "31", "5|||6", "", "{toguestusername}", "1", "2", "1|||1047778690", "0", "n", "n", "n", "y", "y");
INSERT INTO message VALUES("14", "1047785611", "1", "0", "test", "31", "8", "", "{toguestusername}", "2", "3", "1|||1047785611", "0", "n", "n", "n", "y", "y");

This is for ratelog table:
INSERT INTO ratelog VALUES("2", "1", "13", "0", "2", "");
INSERT INTO ratelog VALUES("2", "1", "13", "22", "3", "");
INSERT INTO ratelog VALUES("1", "2", "14", "0", "1", "");
INSERT INTO ratelog VALUES("1", "2", "0", "21", "4", "");

Look, it supposes to display 3 records and 3 records only (topicid=31) in table message. No more or no less. But with the query above, it returns only two records, missing the one with msgid=14. This is hard.

[webm4st0r]

sorry, wasnt paying attention, this should work.

SELECT message.fromuserid, message.msgid, ratelog.userid, ratelog.raterid FROM message LEFT JOIN ratelog ON ratelog.userid = message.fromuserid AND ratelog.raterid = 1 AND ratelog.msgid = message.msgid WHERE topicid = 31 GROUP BY message.msgid

[quanghoc]

But the record with msgid=14 supposes to have rate field with it as 1 not NULL because there is a record in ratelog with userid=1 and msgid=14
This is too complicated huh?
Thanks for the try though.