select two logins from one record

Question

I'm creating chat ( just for two people to chat, not "global" ) and my tables look like this:

MESSAGES:

id | author | receiver | content | sent

where author and receiver are id's from USERS:

id | login | avatar

and i want to get login and avatar both from receiver and author of the message. I was trying with something like this:

SELECT * FROM 
((SELECT messages.*,users.login as starter,users.avatar FROM messages
    LEFT JOIN users ON messages.author = users.id
    WHERE messages.receiver = 1)
UNION 
(SELECT messages.*,users.login as test,users.avatar FROM messages
    LEFT JOIN users ON messages.receiver = users.id
    WHERE messages.author = 1)
    ORDER BY id DESC LIMIT 5) tmp 
ORDER BY id ASC

and few other queries but i could only get one login ( either receiver or author ). Is there any way to do that?

U are using the same id (e.g. 1) for either receiver and author -> u're getting only one login, since is about one and the same user. Guessing they should be different... — wxyz, Nov 02 '13 at 17:31

score 1 · Accepted Answer · answered Nov 02 '13 at 17:40

1

Try this:

select * from messages m
join users a on (m.author=a.id)
join users r on (m.reciever=r.id)

Of course, you can add a where clause to filter it for a specific message, author or whatever.

answered Nov 02 '13 at 17:40

geomagas

3,230
1
17
27

Or whatever. How about using a column list? – Kermit Nov 02 '13 at 18:40

select two logins from one record

1 Answers1