Show correct result with SQL Joins

Question

Firstly, I am newbie to SQL (T-SQL), I would appreciate guidance with this. I have 3 tables with values created as 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 below outcome but seems that i cant or i'm sure i'm doing something wrong.

SQL_Outcom

Please help.

Answer 1

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 "master table", then join them to other tables, after joining them you will have access to their columns.

Hello World :)

UPDATE:

In order to confirm Roger always with id 1 and all the other students in StudentName table, you need to use SET IDENTITY_INSERT which guarantee you the ordering of rows.

Instead of :

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

Do this:

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 2

You need to use inner join and in order to match the records related to each others:

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 3

You need inner join

Try this :

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;