[RESOLVED] MySQL multiple joins on the same table problem

[lpa]

Table "posts" contains articles and 3 last userIDs (us1, us2, us3) of people who posted comments to each article. I need to join "usertable" to get names of last comment posters. This is my query:

$result = mysql_query("SELECT m.id, m.comment, m.us1, m.us2, m.us3,p.name,o.name,t.name,
FROM posts m 
LEFT JOIN usertable p ON m.us1=u.id
LEFT JOIN usertable o ON m.us2=u.id
LEFT JOIN usertable t ON m.us3=u.id   

where m.lid = '$pid' order by m.id desc limit 0,20")  or die('Error');

while(list($id, $comment, $us1, $us2, $us3, $name1, $name2, $name3) = mysql_fetch_row($result)) {
echo "$id<br />";
}

Unfortunately, the "echo"of this result is:

79
79
79
79
79... for all 20 rows,

"79" is the ID of last row in "posts" table. So, why the result of this query is only one row selected from "posts"?

[johanafm]

The posted query won't execute.

Other than that, you should not have 3 user ids in onw row: see database normalization

[cretaceous]

You haven't put in a table for the alias 'u' - but you don't need to as this bit should be:
LEFT JOIN usertable p ON m.us1=p.id
LEFT JOIN usertable o ON m.us2=o.id
LEFT JOIN usertable t ON m.us3=t.id

Anyway as Johanafm almost implies it would be much better to add a third relationship table which only records id of user matched to id of article