CODEWITHSUNDEEP
sql
dsm309
dsm309

Reputation: 69

SQL- Looking up value across tables

I have two tables as below: 

Highschooler
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

Friends
ID1 ID2
1689    1709
1709    1689
1782    1709
1911    1247
1247    1468
1641    1468
1316    1304
1501    1934
1934    1501
1025    1101

I need to create a table that has the name and grade of people who are friends with each other as given in the "Friends" table. I basically need to look up the name and grade of each ID given in the friends table.

I have two select statements that look up the name and grade for ID1 and ID2 separately. 

select name,grade from highschooler where id IN
(select id1 from friends)

and 

select name,grade from highschooler where id IN
(select id2 from friends)

However, I cannot figure out how to get a table that looks like this:

name1 grade1 name2 grade2
Gabriel 9    Cassandra 9

I have tried concatanating the two select statements as well as union but neither work for this situation.

I am new to SQL. Any help is very much appreciated!

Upvotes: 0

Views: 59

Answers (3)

No'am Newman
No'am Newman

Reputation: 6477

You need to join the 'highschooler' table twice:

select h1.name, h1.grade, h2.name, h2.grade
from highschooler h1 
inner join friends on friends.id1 = h1.id
inner join highschooler h2
on h2.id = friends.id2

The problem with the 'friends' table and the above query is that even if A is a friend of B, B is not necessarily a friend of A!

Upvotes: 3

Vignesh Kumar A
Vignesh Kumar A

Reputation: 28403

Try this

SELECT T1.name,T1.grade,T2.name,T2.grade
FROM highschooler T1 
LEFT Join Friends F ON F.id1 = T1.id
LEFT Join highschooler T2
on T2.id = F.id2

Upvotes: 1

cvkline
cvkline

Reputation: 454

As previous poster mentioned, you need to list the highschooler table twice, since you need to reference it twice to compare both the ID's from the friends table. His syntax is correct; another way to do it with straight joins and a where clause is:

SELECT side1.name, side1.grade, side2.name, side2.grade
   FROM highschooler AS side1, highschooler AS side2, friends
   WHERE (side1.id = friends.id1 AND side2.id = friends.id2);

As pointed out, though, this only treats friends as one-way relationships. In your "friends" table, I do see that some "bidirectional friends" are listed using two rows with the IDs in the opposite order. If in fact you want to treat all friend relations as bidirectional, you have to compare twice with the columns in the "friends" table listed in both orders.

SELECT DISTINCT side1.name, side1.grade, side2.name, side2.grade
   FROM highschooler AS side1, highschooler AS side2, friends
   WHERE (side1.id = friends.id1 AND side2.id = friends.id2) OR
   (side1.id = friends.id2 AND side2.id = friends.id1);

Notice I had to use DISTINCT here, because otherwise, if a friendship is listed twice in the opposite order (as in 1689,1709 and 1709,1689), it gets listed twice in the result; DISTINCT removes the duplicate.

I guess which query you use depends on if you consider friends to be "Facebook-style" friends or "Twitter-style" friends. :)

Upvotes: 2

Related Questions