SQL SELECT help

dirkoneill

I have a fair amount of experiece with SQL but i have never tried to do a statement like this and i'm not sure if what i want is possible. either that or my brain has just shutdown already at 10 in the morning. (gonna be a long day if it has) ok first of all i have 2 tables:

members { id, nickname }
messages {id, sender_id, reciever_id, message }

I am trying to return the lastest message and display who it is from and who it is to. not a problem except right now it's just returning the sender_id and reciever_id. how do i write a statement that will also look up the names of both. for instance right now my result is looking something like this:

id | message | sender_id | reciever_id | nickname
1 | "hi" | 1 | 9 | dirkoneill

i want the result to look more like this

id | message | sender_id | reciever_id | sender_nick | rec_nick
1 | "hi" | 1 | 9 | dirkoneill | joe

right now my sql statement is as follows:

SELECT messages.*, members.nickname FROM messages, members WHERE (recieve_id = '9' AND sender_id = '1') OR (recieve_id = '1' AND sender_id = '9) AND members.id = messages.sender_id

that is supposed to display all messages thast have been written by either member that are sent to each other. thanks for the help

AshtrayWaterloo

Look into an INNER JOIN statement, should be really easy to do.

So easy I'll do it myself right now :-)

SELECT messages.*, members.nickname FROM messages INNER JOIN members ON
members.id = messages.sender_id
WHERE (recieve_id = '9' AND sender_id = '1') OR (recieve_id = '1' AND sender_id = '9)

That should do it.

Ash

dirkoneill

thanks for the tip. i think that's the right way but it still is only giving me the nickname of the sender. i also want the nickname of the reciever in the resulting row. that is my problem. even if i add INNER JOIN members ON messages.sender_id = members.ID AND messages.recieve_id = members.ID it only gives me the sender's nickname.

AshtrayWaterloo

Ah, I understand now. The trick here is to add ANOTHER inner join to the members table with another alias on that table:

SELECT messages.*, members.nickname, members2.nickname FROM messages INNER JOIN members ON
members.id = messages.sender_id
INNER JOIN members AS members2 ON
members2.id = messages.reciever_id
WHERE (recieve_id = '9' AND sender_id = '1') OR (recieve_id = '1' AND sender_id = '9)

And that should do it for you.

Ash

dirkoneill

genious. that was exactly what i wanted. thanks

AshtrayWaterloo

Cheers!

Ash