Mysql - how to get data for two foreign key columns of same table
mysql, php, sql
Solution
SELECT u1.username AS "sender", u2.username AS "recipient", m.message, m.from, m.to
FROM `users_messages` m
LEFT JOIN `users` u1 ON (u1.id = m.from)
LEFT JOIN `users` u2 ON (u2.id = m.to)
WHERE m.from = XX OR m.to = XX
Problem
I have two tables in my database Table: `users` with columns: `id`, `username`, `email` etcs Table: `users_messages` with columns: `id`, `to`, `from`, `messages`, `sent_time` Columns `to` and `from` are keys `users` from `users` table. Issue is that I need to: "select all users to and from username and message". For instance if table row is like below: ``` From, To, Message 11, 14, Hi, How are you 14, 11, Hello, ``` and if 11 is john and 14 is mark, then I need output like this: ``` From, To, Message john, mark, Hi, How are you mark, john, Hello, ``` With my query I can only get username for from column or to column but not for both. How can I perform a query that gives me from and to both usernames? The following query gives me empty result: ``` SELECT u.username, m.message, m.from, m.to FROM `users_messages` m, `users` u where m.from = u.id and m.to = u.id LIMIT 0 , 30 ``` I have tried other queries, but not able to get the desired output. Do I need a self join as well for messages table?