CODEWITHSUNDEEP
mysql
Amy Neville
Amy Neville

Reputation: 10611

Reference outside inner join query

I have the following query for a basic messaging system. It shows the messages that you've sent. However, it is showing the first_name and second_name for the account referenced by sender_id. Instead I want it to output the name columns referenced by receiver_id

ie) SELECT first_name, second_name WHERE accounts.account_id = messages.receiver_id

Do I need to write a seperate query for this or can I make this one output it in a single query? I've been thinking quite hard about this problem!

SELECT first_name, second_name, message_id, title, body, seen, urgent, type, messages.timestamp 
FROM messages 
INNER JOIN accounts 
ON messages.sender_id=accounts.account_id 
WHERE sender_id = ?

Upvotes: 0

Views: 97

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270371

You can do this with two joins:

SELECT s.first_name as sender_first, s.second_name as sender_last,
       r.first_name as rec_first, r.second_name as rec_last,
       m.message_id, m.title, m.body, m.seen, m.urgent, m.type, m.timestamp 
FROM messages m inner join
     accounts s
     ON m.sender_id = s.account_id inner join
     accounts r
     on m.receiver_id = r.account_id
WHERE m.sender_id = ?

Upvotes: 1

Related Questions