MS SQL DB retrieve two coloumns from one query

Question

I have two tables as below:

1. Students table

   

2. work table:

   

My query is:

select stud_name
from students
  inner join work on work.library=students.stud_id
where work_id=2

It returns name of student correctly. But please tell me is it possible to retrieve two students name via inner join . Example : get RON and JACK name as the out put in a single result set.

I tried:

select  stud_name,stud_name
from students
  inner join work on work.library=students.stud_id,work.sports=students.stud_id
where work_id=2

But as expected I got a massive error.

Answer 1

Try

Select a.stud_name, b.stud_name from students a, students b, work w
where w.work_id = 2
and a.stud_id = w.library
and b.stud_id = w.sports

The work table is the central part in this and you need the students table twice indepently to get each student.