CCardeno CCardeno - 1 month ago 7
SQL Question

How to show two columns of names?

SQL question

I have a table with Students (ID and Name)

I have another Table with Friends (ID1 and ID2)

create table Students(ID int, name text);
create table Friends(ID1 int, ID2 int);


If I enter:

SELECT Friends.ID1, Friends.ID2 FROM Friends


I get:

http://image.prntscr.com/image/d6e599ee5bd74c13822339afeb4c05ab.png

I think I need to use:

SELECT Strudets.Name
from Friends, Strudents
Where Strudents.ID=Friends.ID


And I want to show two columns of names

I need to look for the Friends.ID1 in the Students Table and get the Strudents.name, then do the same for Friends.ID2 and then show both names

EDIT with more info

When I enter this:



SELECT id1, name
FROM friends
INNER JOIN students
ON friends.id1 = students.id





I get this:

Lista de una sola columna

And I need to show two columns of names

ANSWER FROM @kbbal



SELECT s1.NAME, s2.NAME
FROM FRIENDS
INNER JOIN STUDENT s1 ON FRIENDS.ID1 = s1.ID
INNER JOIN STUDENT s2 ON FRIENDS.ID2 = s2.ID





I love this website!! <3

Answer

You seem new so I will lay out an approach.

  1. Join the friends table to the students table. You should do this for the first friend first, so join the tables on the common ids. This would be the id column in the students table and id1 in the friends table.
  2. Join the students table again. This time perform the join on id2 of the friends table
  3. Specify that you want id1, name, id2, name in the SELECT statement

Your final answer should give you the ids of both friends and the names corresponding to their ids in the students table.

Edit based on comments:

Here is how you would do the joins:

SELECT friends.id1, s1.name, friends.id2, s2.name
FROM friends
INNER JOIN students s1
ON friends.id1 = s1.id
INNER JOIN students s2
ON friends id2 = s2.id

The trick is to actually join the students table twice, once for each friend. And then you just have to create aliases (s1 and s2) so you can distinguish between the two instances.