CODEWITHSUNDEEP
sql
Alexandru
Alexandru

Reputation: 21

A join retrieve null column

I have 2 tabels 

create table Students(
SerialNumber int primary key identity,
                    Name varchar(50) not null,
                    Surname varchar(50) not null,
                    AcademicProgram int foreign key references AcademicProgrammes(Id)
                 )
Create table AcademicProgrammes(
                                Id int primary key identity,
                                Name varchar (20) not null
                           )

and I want to get from students table all the students, but instead the AcademicProgram reference foreign key I want the name of the AcademicProgrammes.

my join looks like this : 

select Students.SerialNumber,Students.Name, Students.Surname, AcademicProgrammes.Name
from Students left join
AcademicProgrammes on Students.SerialNumber=AcademicProgrammes.Id

if i have 2 academic programs master's and undergraduate as a result I get all ste students but as the academic program name column only the first 2 students have the name of de academic program, and the rest of them have null

Vasile  Magdalena-Maria Licenta
Ciotmonda   Oana-Maria  Master
Rus Diana   NULL
Turcu   Gabriel NULL

I can't find what I'm doing wrong

Thanks !

Upvotes: 2

Views: 59

Answers (1)

peterm
peterm

Reputation: 92785

I believe you need to join by

Students.AcademicProgram=AcademicProgrammes.Id

instead of

Students.SerialNumber=AcademicProgrammes.Id

Because of that you're getting names of academic programs for only students with serial numbers 1 and 2 (since you have only two programs).

Therefore try following

SELECT s.SerialNumber,
       s.Name, 
       s.Surname, 
       a.Name AS Program
  FROM Students s LEFT JOIN
       AcademicProgrammes a ON s.AcademicProgram=a.Id

Upvotes: 2

Related Questions