Self-joints - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Administration

Self-joints

Author

Topic

tats
Starting Member

5 Posts

Posted - 2014-12-11 : 15:31:37

Hi all,

I would appreciate a help with this exercise:

"For every situation where student A likes student B, but we have no information about whom B likes (that is, B does not appear as an ID1 in the Likes table), return A and B's names and grades".

My question is what is the way to find B in ID1 in Likes table? The script that I have written and that gives empty results is:
(Notes: In Highschooler table one finds - student ID, Name, Grade
In Likes table one finds - ID1, ID2 (of students who mutually or onesidedly like each other))

Select H1.name, H1.grade, H2.name, H2.grade
From Highschooler H1 inner join Highschooler H2 using(ID)
inner join
(select L1.ID1, L2.ID2
From Likes L1 inner join Likes L2
on L1.ID1 = L2.ID1 and L1.ID2 = L2.ID2
Where L1.ID2 not in(
select L1.ID1
From Likes L1 inner join Likes L2
on L1.ID1 = L2.ID1 and L1.ID2 = L2.ID2) on ID = ID1)

Thanks a lot for any hints/help!!

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2014-12-11 : 20:20:36

Can we see the table schema? Referential integrity? Data?

No amount of belief makes something a fact. -James Randi

tats
Starting Member

5 Posts

Posted - 2014-12-12 : 04:42:55

Hi,

Here are the two tables below. Many thanks in advance for a help!

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

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

tats
Starting Member

5 Posts

Posted - 2014-12-12 : 06:30:03

I have solved this, please, never mind :)

tats
Starting Member

5 Posts

Posted - 2014-12-12 : 06:40:14

I have another question though, is someone could help.

I have created this table of paired students: ID1, name1, grade1, ID2, Name2, grade2
Select distinct F1.ID1, H1.name, H1.Grade, F2.ID2, H2.name, H2.Grade
From Friend F1 inner join Highschooler H1 on F1.ID1 = H1.ID
Inner join Friend F2 on F1.ID2 = F2.ID2
Inner join Highschooler H2 on H2.ID = F2.ID2

From here I need to select the students who has a pair only in the same grade (If a student has a pair in another grade at the same time, I should exclude him/her). Any suggestions how can I do that?

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

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-12-12 : 08:53:26

Please stop posting your homework. if you need help, ask your instructor or their assistant

tats
Starting Member

5 Posts

Posted - 2014-12-12 : 09:10:46

well, I am just doing an online course, where unfortunately there is no instructor to help with questions. I was sure forums are to discuss and get help with issues that one does not know. Probably I am not in the right forum, there will be no other posts.

On the last note, I solved also this exercise.

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2014-12-12 : 11:20:05

It does seem that you are fully capable of deriving the solutions on your own. So far you are two for two.

No amount of belief makes something a fact. -James Randi

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources