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

can you give sample records? – John Woo Apr 05 '13 at 15:55 — John Woo, Apr 05 '13 at 15:55

Taryn · Accepted Answer · 2013-04-05T16:47:24.620

2

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

edited Apr 05 '13 at 16:47

answered Apr 05 '13 at 15:56

Taryn

242,637
56
362
405

I changed my query similar to this. The number of the result is same as above(not include the skill level =0) Also all the skill name for the person is same.. – Michael Apr 05 '13 at 16:13
@Michael My suggestion would be to edit your original post with some sample data from each table. – Taryn Apr 05 '13 at 16:14
@Michael see my edit, I updated the query to include the `SkillLevel` table. – Taryn Apr 05 '13 at 16:35
Thank you very much. Your updated fix the name problem. But it doesn't display the skills that has level =0. I added the sample data. – Michael Apr 05 '13 at 16:47
@Michael Perfect the sample data was needed to determine the query. See my edit. – Taryn Apr 05 '13 at 16:47
Thank you so much. I need spend sometimes to understand the query. :) – Michael Apr 05 '13 at 17:13
@Michael I am glad it works. FYI - The subquery with the `CROSS JOIN` is assigning each skill to each user, then you join that back to the other tables to determine what skills each person actually has. :) – Taryn Apr 05 '13 at 17:15

score 0 · Answer 2 · answered Apr 05 '13 at 15:53

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.

score 0 · Answer 3 · edited May 23 '17 at 12:03

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

What Join should I use in SQL

3 Answers3