What Join should I use in SQL

Question

I have four tables in my Database: Person that contains ID(PK) and Name. Person_Skill that contains ID(PK), PID(FK), Skill(FK) and SkillLevel(FK). Skill that contains ID(PK) and SkillLabel. SkillLevel that contains ID(PK) and Name.

Every skill has level from 0 to 7

Now I want to display all the skill that the person has(Include the skilllevel = 0)

Select
   [dbo].Person.Name as Name,
   [Skill].SkillLabel as SkillName,
   [Person_Skill].[SkillLevel] as SkillLevel
From
   ([dbo].Person inner join [dbo].[Person_Skill] ON [dbo].[Person_Skill].PID= Person.ID)
   inner join [dbo].[Skill] ON [dbo].[Person_Skill].Skill=Skill.ID

The above code only display the skill that person has from level 1 to level 7.

I believe the reason I only get the skill from level 1 to 7 is because the person table only contains the skill from level 1 to 7, but I'm not sure about this. I got the database from other. If my assumption is correct, is there anyway to do this? To get all the skills in the skill table and display the skill level that the person has(Include skillllevel =0).

Sample Data:
Person
ID    Name
----------
1     Michael
2     Alex

Person_Skill
ID    PID  SkillID Skill_Level
5     1     10          5
6     2     11          1
7     1     12          7
8     1     13          5

Skill
ID    Name
10    java
11    C++
12    HTML
13    ASP
14    C
15    .NET
16    C#
17    Objective

The expect results are;
Name    SkillName SkillLevel
----------------------------
Alex    java        0
Alex    C++         1
Alex    HTML        0
Alex    ASP         0
Alex    C           0
Alex    .NET        0
Alex    C#          0
Alex    Objective C 0
Michael    java      5
Michael    C++       0
Michael    HTML      7
Michael    ASP       0
Michael    C         0
Michael    .NET      5
Michael    C#        0
Michael    Objective C0

The current query only output

Alex    C++         1
Michael    java      5
Michael    HTML      7
Michael    .NET      5

Answer 1

Edit, if you want to return all skill names for each person, then you will want to use:

select d.p_name,
  d.s_name SkillName,
  coalesce(ps.Skill_Level, 0) Skill_Level
from
(
  select p.id p_id, p.name p_name, s.id s_id, s.name s_name
  from person p
  cross join skill s
) d
left join person_skill ps
  on d.p_id = ps.pid
  and d.s_id = ps.skillid
left join skill s
  on ps.skillid = s.id

See SQL Fiddle with Demo

If you want to include all Skills 0-7, then you will want to use a LEFT JOIN. You query will be similar to the following:

select p.Name as Name,
   s.SkillLabel as SkillName,
   ps.[SkillLevel] as SkillLevel
from [dbo].[Skill] s
left join [dbo].[Person_Skill] ps
  on ps.Skill=s.ID
left join [dbo].Person p
  on ps.PID = p.ID

Edit, without seeing any data it is difficult to determine. But if you want to retrieve all SkillLevels, then you will need to include that table. You might need to use:

select 
  p.Name as Name,
  s.SkillLabel as SkillName,
  sl.[Name] as SkillLevel
from SkillLevel sl
left join [dbo].[Person_Skill] ps
  on ps.SkillLevel=sl.ID
left join [dbo].[Skill] s
  on ps.Skill = s.Id
left join [dbo].Person p
  on ps.PID = p.ID

Answer 2

An INNER JOIN will only return rows where there is a match on both sides. So in your code if a person does not have a skill with level 0 it would not be returned.

You could do a LEFT or RIGHT join and these get all the rows from the table on either the left or the right side. I think you probably want to use a left join, but without knowing more about your schema it's hard to say for sure. See the answer given to the question Left join and Left outer join in SQL Server for more detail on the differences in different join types

Answer 3

You would want to use a LEFT JOIN which when tableA is inner joined on tableb would return all records from tableA regardless of whether or not there was a match from tableB

Therefore, if there are no persons with a skill of 0, you will still get back all of the person records.