Danielius Danielius - 7 months ago 18
SQL Question

Getting info about two users with JOIN

I got a problem. I am making a Private messages system now, and I need to get user and his rank info to print it on screen. So I do JOIN

SELECT
private_messages.from as `from`,
private_messages.to as `to`,
private_messages.theme as `theme`,
private_messages.date as `date`,
private_messages.message as `message`,
users.username as `username`,
users.id as `user_id`,
private_messages.id as `pm_id`,
ranks.style as `style`,
ranks.rank_name as `rank_name`
FROM `private_messages`
LEFT JOIN `users` ON private_messages.from = users.id
LEFT JOIN `ranks` ON users.rank = ranks.id
WHERE private_messages.id=? LIMIT 1


But the problem is that I get only info about user 'from' (who sended a message) and dont get rank and other things about user 'to' (who received a message) and I need his info too and don't know what to do, can anyone help me? :)

Answer

If you want infor about two user you must join two time the table user one for user_from an one for user_to ...

this is just a suggestion for getting info for two users

SELECT 
    private_messages.from as `from`,
    private_messages.to as `to`,
    private_messages.theme as `theme`,
    private_messages.date as `date`,
    private_messages.message as `message`,
    users.username as `username`,
    users_to.username as `username_to`
    users.id as `user_id`,
    private_messages.id as `pm_id`,
    ranks.style as `style`,
    ranks.rank_name as `rank_name`
    FROM `private_messages` 
    INNER JOIN `users` ON private_messages.from = users.id
    INNER JOIN `users` as user_to ON private_messages.to = users.id
    LEFT JOIN `ranks` ON users.rank = ranks.id
    WHERE   private_messages.id=? LIMIT 1
Comments