expanding a mysql JOIN query in PHP

Question

I need to alter my existing JOIN query below to also include the data from users.image correlating to the UserID of the post maker. Something like:

users.image WHERE users.UserID = posts.userid

I am not very good with join queries yet. How would I do this?

Existing Query:

$result = mysql_query("SELECT  posts.* FROM  listen JOIN posts ON posts.userid = listen.listenid WHERE listen.userid = '$user_id' ORDER BY DATE desc") or die(mysql_error());

Answer 1

Just add another JOIN clause:

SELECT  posts.* 
FROM  listen 
JOIN posts ON (posts.userid = listen.listenid) 
JOIN users ON (users.UserID = posts.userid)
WHERE listen.userid = '$user_id' 
ORDER BY DATE desc

You may need to change the JOIN to a specific join such as LEFT JOIN, depending on what you're after.

Btw, it is easier to see the query on multiple lines.

Edit: You'll probably want to add additional items that you are selecting with your fields, such as SELECT posts.*, users.*