Show correct result using SQL joins

Question

Firstly, I am new to SQL (T-SQL), I would appreciate a guide. I have 3 tables with values ​​created as shown below.

CREATE Table StudentProject (ID int identity (1,1) PRIMARY KEY, ProjectName Varchar (30), DueDate Date) CREATE Table StudentName (ID int identity (1,1) PRIMARY KEY, StudentName Varchar (30)) CREATE Table StudentWork (ID int identity (1,1) PRIMARY KEY, ProjectID int, StudentID int) Insert Into StudentProject values ('Omega','1/2/2005'),('KingOmega','1/3/2000'),('Beast','1/6/2007'), ('DeltaMovie','3/7/2008') Insert into StudentName values ('Roger'),('John'),('James'),('Juliet'),('William') Insert into StudentWork values (1,1),(1,2),(2,2),(2,3),(3,3),(3,4),(1,3) 

The goal is to produce the result below, but it seems like I can't or I'm sure I'm doing something wrong.

SQL_Outcom

Please, help.

Answer 1

You need to use an internal join and to match the records linked to each other:

 Select p.ProjectName, s.StudentName from StudentName s Inner join Studentwork sw on sw.studentid = s.Id inner join StudentProject p on p.ID= sw.ProjectId Order by P.ProjectName desc 

Answer 2

 SELECT StudentProject.ProjectName, StudentName.StudentName FROM StudentWork INNER JOIN StudentProject ON StudentProject.ID = StudentWork.ProjectID INNER JOIN StudentName ON StudentName.ID = StudentWork.StudentID 

You have 3 tables, try to recognize the "main table", and then join them to other tables, after joining them you will get access to your columns.

Hello World :)

UPDATE:

To confirm that Roger always has identifier 1 and all other students in the StudentName table, you need to use SET IDENTITY_INSERT , which ensures that the rows are ordered.

Instead:

 Insert into StudentName values ('Roger'),('John'),('James'),('Juliet'),('William') 

Do it:

 SET IDENTITY_INSERT StudentName ON Insert into StudentName values (1,'Roger'),(2,'John'),(3,'James'),(4,'Juliet'),(5,'William') SET IDENTITY_INSERT StudentName OFF 

Answer 3

You need inner join

Try the following:

 select s.StudentName,p.ProjectName from StudentName s inner join StudentProject sp on sp.id= sw.ProjectId inner join StudentWork sw on sw.studentid = s.id;