[ How do I join multiple columns in one table to a column in another table ]
I am quite new to SQL, most of my other questions I have been able to answer by searching but this one seemed harder to find something similar. I did see this and this which seemed similar, but I was not able to find an answer although I think it should be straightforward.
To simplify my database, I am trying to join two of my tables into a view.
The one table (a_main) has a mapping between a user ID (UID) and their user name (name) The second table (log_points) has a number of columns including two users (user1 and user2). The two users are identified by user ID, but I would like to show their user names in the view.
SELECT name, UID FROM a_main
returns something like
SELECT date, user1, user2 FROM log_points
2013-03-22 1222 1234
I would like the view to return the users by their name instead of id.
If it was just one user, I could do a straightforward JOIN
SELECT date, a_main.name FROM log_points INNER JOIN a_main ON log_points.user1 = a_main.UID
but I can't figure out how to also join user2 to a_main.UID.
Any suggestions would be greatly appreciated! I am using MySQL.
EDIT found the answer here.
SELECT t1.name AS person1, t2.name AS person2 FROM log_points t INNER JOIN a_main t1 ON t1.UID = t.user1 INNER JOIN a_main t2 ON t2.UID = t.user1;
SELECT t1.name AS person1, t2.name AS person2 FROM log_points t JOIN a_main t1 ON t1.UID = t.user1 JOIN a_main t2 ON t2.UID = t.user1;
SELECT date, (SELECT name FROM a_main where UID=user1)as user1, (SELECT name FROM a_main where UID=user2)as user2 FROM log_points.
Can any give insight on performance both queries.