"Inserting" columns neatly

Question

I have one table, called "likes",that looks like this:

ID1     ID2
1247    1468
1641    1468
1316    1304
1025    1101

And another table, called "Students", that looks like this:

ID      Name    Grade
1510    Jordan  9
1689    Gabriel 9
1381    Tiffany 9
1709    Cassandra   9
1101    Haley   10
1782    Andrew  10
1468    Kris    10
1641    Brittany    10
1247    Alexis  11
1316    Austin  11
1911    Gabriel 11
1501    Jessica 11
1304    Jordan  12
1025    John    12
1934    Kyle    12
1661    Logan   12

What I'm trying to do is return a new table that has the following columns:

NameofID1, GradeofID1, NameOfID2, GradeOfID2, which would equate to somewhat of an "insert".

Any idea of how I might do this?

Thanks!

I removed the incompatible database tags. Please tag with the database you are really using. — Gordon Linoff, Feb 09 '17 at 21:55
*What I'm trying to do is return a new table*. You can't return a table. Are you trying to write a query? Create a view? Create a new table? — shmosel, Feb 09 '17 at 21:55
Possible duplicate of [MySQL pivot table](http://stackoverflow.com/questions/7674786/mysql-pivot-table) — shmosel, Feb 09 '17 at 21:56
Sorry about the server mixup. I am new to SQL and I initially tagged it sql-server but SO suggested mysql as well. — bst-for-life, Feb 09 '17 at 22:03

score 1 · Answer 1 · answered Feb 09 '17 at 21:57

1

You need to join the students table twice.

--insert into newtable(col1,col2,col3,col4)
select s1.name as name1,s2.name as name2,s1.grade as grade1,s2.grade as grade2
from likes l
join students s1 on s1.id=l.id1
join students s2 on s2.id=l.id2

answered Feb 09 '17 at 21:57

Vamsi Prabhala

48,685
4
36
58

score 0 · Answer 2 · answered Feb 09 '17 at 22:00

0

You just need to join to the table twice:

Select s1.Name,s1.grade,s2.Name,s2.grade,
  from  likes l
   inner join students s1
    on l.ID1 = s1.ID
   inner join  students s2
    on l.id2 = s2.ID
  Where l.ID1 = 1247

answered Feb 09 '17 at 22:00

Dys1

90
8

score 0 · Answer 3 · answered Feb 09 '17 at 22:01

You can use simple INNER JOIN to get the values, e.g.:

SELECT s1.name, s1.grade, s2.name, s2.grade
FROM student s1 JOIN likes l ON s1.id = l.id1
JOIN student s2 ON s2.id = l.id2;

Here's the SQL Fiddle.

Once you get the values, you can use INSERT INTO.. SELECT statement to insert into new table.

Here's the documentation and examples for INSERT INTO... SELECT statememt.

"Inserting" columns neatly

3 Answers3