SOLVED SQL question (multiple table select)

This is driving me nuts.

So, I did a simple query to link two tables together to do such things as make a user id into a username for display.


That is a simple working example. It simply displays usernames for every "from userid" and it does this by matching to the userid in the users table. Simple, works, I'm happy.

Now, I cannot figure out how to do the more complicated one.

See, the problem is I need to make two identical selects almost...


I'm missing something because that is gibberish in SQL speak.

How do I solve this problem in one SQL line? I know this has to be solvable because it makes no sense to not be able to do it... but I have no idea how to do it.

Help?

What are you actually trying to do?

You need to figure out what information you're trying to get. More specifically the driver. If you need to get the messages to and from a user, select both of those tables.

I assume you have a messages table: messages_table

Select m.messages_to_userid as "To User", m.messages_from_userid as "From User"
From messages_table m

This will get you the id's users in those messages.

Do an Inner join or a left outer join depending on if all the values in the message table will have a result.

Select m.messages_to_userid as "To User", m.messages_from_userid as "From User", u1.username as "To Name", u2.username as "From Name"
From messages_table m
INNER JOIN users_table u1 ON u1.userid = m.messages_to_userid
INNER JOIN users_table u2 ON u2.userid = m.messages_from_userid

Yay, I found a book which helped me.

It is called a Self Join. In short, you can only compare against a table once but you can refer to it multiple times.

That works.

So, if you run into this problem as I have, now you know how to get around it.

@Quigsby

I never really used the Inner Join or Outer Join language all that much... I know the = in the where cause is an equijoin (or just called an inner join) but I generally haven't used that language much so I'm not too familiar with it.

In short, what I was trying to do was...

You have two UIDs in the messages table and you want to make them into usernames. There is a usernames table for this where a UID = username. So, before I would just join them on UID but since I have to check two UIDs per row (the from and the to), the way I was using before wasn't working.

I found an Oracle book which generally explained how a "self join" worked which resolved that particular problem. The php/mysql book I referred to earlier before creating this thread assumed that an advanced topic was a simple equijoin and wouldn't cover the problem I was trying to fix.

Ah the self join. One of those things that are so obvious it's normally missed.

While I am glad to see that you figured out your query, if you want to learn SQL, I cannot recommend SQL Queries for Mere Mortals enough. That book not only teaches advanced techniques in SQL, it helps to think about problems in a way that is more readily translated into SQL.

Thanks for the recommendation. I will keep an eye out for that book.