Mysql - Multiple joins but to same table?

[wh33t]

Hey SP,

I’m curious what sql query would allow me to select multiple image file names and usernames on a page join. Here is my messages table. It’s for private messages.

message_idint(11) Auto Increment
message_parentint(11) [0]If this message is part of a thread
user_idint(11)The user who sent the message
recipient_idint(11)The user who is receiving the message
message_readtinyint(1) [0]Whether the message had been viewed before
mressage_repliedtinyint(1) [0]Has the message been replied?
message_titlevarchar(255)The title of the message (subject)
message_bodytextThe body of the message
date_createdint(11)The date the message was sent
date_editedint(11)The date the message was edited

So what I want to do is join the users table to grab the username of user_id, and the username from recipient_id.

Furthermore I also want to join the images table to grab the avatars of the user_id of the message

How can I pull this off?

Here is my other two tables.

user_idint(11) Auto IncrementThe Unique User ID
usernamevarchar(255)The Username
passwordvarchar(255)The Password
password_oldvarchar(255)Their old password, used for recovering an account if someone else changes their password.
emailvarchar(255)The email address
email_confirmedtinyint(1) [0]Whether this email address has been confirmed
email_subverkeyvarchar(100)The randomly generated string used to verify or unsubscribe an email address
user_leveltinyint(1) [0]The user level
tasks_enabledtinyint(1) [0]Whether or not the user can use task manager
tasks_color_codevarchar(50)
date_createdint(11)
date_editedint(11)

image_idint(11) Auto IncrementPrimary Key
user_idint(11)The User who uploaded it
image_functiontinyint(1)Where its an avatar, video thumbnail, forum upload etc
image_original_filenamevarchar(255)The Original Image Filename
image_original_wint(11)Original image W
image_original_hint(11)Original Image H
image_small_filenamevarchar(255)The Small Image Filename
image_small_wint(11)Small Image H
image_small_hint(11)Small Image H
image_medium_filenamevarchar(255)The Medium Image Filename
image_medium_wint(11)Medium Image H
image_medium_hint(11)Medium Image H
image_large_filenamevarchar(255)The Large Image Filename
image_large_hint(11)Large Image H
image_large_wint(11)Large Image H
date_createdint(11)The date the image was uploaded
date_editedint(11)The date the image was last edited

Here is my current sql attempt which is falling short of being able to grab the username/avatar image of the user_id from the messages table

SELECT
m.message_id, m.message_parent, m.recipient_id, m.message_read, m.message_title, m.message_body, m.date_created,
u.username, u.user_id,
img.image_small_filename, img.image_small_w, img.image_small_h
FROM
messages m
LEFT JOIN
users u ON u.user_id = m.recipient_id
LEFT JOIN
images img ON u.user_id = img.user_id
WHERE
m.recipient_id = “’ . $_SESSION[‘user’][‘user_id’] . '” AND
img.image_function = “1”

[r937]

SELECT m.message_id
     , m.message_parent
     , m.recipient_id
     , r.username         AS recipient
     , m.message_read
     , m.message_title
     , m.message_body
     , m.date_created
     , u.username
     , u.user_id
     , img.image_small_filename
     , img.image_small_w
     , img.image_small_h
  FROM messages m
INNER
  JOIN users r 
    ON r.user_id = m.recipient_id
INNER
  JOIN users u 
    ON u.user_id = m.user_id
INNER
  JOIN images img 
    ON img.user_id = u.user_id
   AND img.image_function = 1
 WHERE m.recipient_id = ' . $SESSION['user']['userid'] . ' 

[wh33t]

Mysql is sooooo amazing! It’s like anything you want it to do, it’s possible as long as you go to the sitepoint forums and catch r937!

Thanks again! You’ve helped me so much.