How can I select data from two tables (using an intermediate table)?

[Twinbird]

Hello,

 

I have three tables, like so:

 

user:

id | name

-----------

1   Bob

2   Jim

3   Alex

...

 

student:

id | name

-----------

1   Sarah

2   Hans

3   Kim

...

 

user_student   <--- this is an intermediate table linking users to students

user_id | student_id

-------------------------

1            2

1            3

2            1

...

 

 

Is there an easy way (i.e. using a single query) to display all user_student links, but instead of showing:

1            2

1            3

2            1

...

 

It would show:

Bob    Hans

Bob    Kim

Jim     Sarah

...

 

I found this related thread, but I'm looking for something a little more basic.

 

Thanks!

[gizmola]

Yes there is a simple way, and it's named "join", or "joining" the tables together.

 

There are several syntaxes you can use to do the same thing.  In this case, all your joins are going to be "inner" joins, so what you want is to use "Left Inner" joins from your user_student table back to user and student, respectively.

 

SELECT u.name, s.name
FROM user_student us
LEFT JOIN user u ON u.id = us.user_id
LEFT JOIN student s ON s.id = us.student_id

This person created a nice site that really breaks down and makes clear the syntax and the different variations you can use. http://mysqljoin.com/

[Twinbird]

EDIT: Thanks for the reply gizmola! I figured it out Didn't think it would be so simple.

 

Well, this seems to do exactly what I want:

SELECT student.name AS sName, user.name AS uName FROM student_user INNER JOIN student ON student.id = student_user.student_id INNER JOIN user ON user.id = student_user.user_id;

Problem solved. Usually things don't work out this fast for me ...

Edited November 25, 2013 by Twinbird