Help with a MySQL join

Question

I have a Skills table which has 14 records in it. It is structured like this: | id | name |

I then have a User_Skills table which I want to join to the skills table. Here's is structure: | User_id | Skill_id |

Basically I want to be able to do a join which always results in 14 records and shows NULL in any fields the user isn't associated with.

I almost got the result I want with this query Code:

SELECT * FROM Skills LEFT JOIN User_Skills ON Skills.id = User_Skills.Skill_id

Edit: To make it clearer - this is the set of results with this query. I need to select the skill by the User_id, but still show all 14 skills even if the result is Null in the User_id field.

id  name  User_id  Skill_id
1   HGV1  1000  1
1   HGV1  1001  1
2   HGV2  1001  2
3   No.1 Mechanic  1000  3
3   No.1 Mechanic  1001  3
4   No.2 Mechanic  1001  4
5   Designer  1001  5
5   Designer  1000  5
6   Engineer  1000  6
7   Data Analysis  1001  7
7   Data Analysis  1000  7
8   Coaching/Inst.  1001  8
8   Coaching/Inst.  1000  8
9   Hospitality  1000  9
10  Promo Girl  1000  10
11  Public Relations  NULL  NULL
12  Photographer  NULL  NULL
13  Film Crew  1001  13
14  Physiotherapist  NULL  NULL

but it displays the amount of records I have in the user_skills table... ie, duplicate skill names because more than one user is associated with the skill.

This too, almost gets it: Code:

SELECT * FROM Skills LEFT JOIN User_Skills ON Skills.id = User_Skills.Skill_id WHERE User_id = 1001

but this shows only the skills the user is associated with and doesn't show any NULL entries like I want.

Does anyone know how I can achieve this query?

Answer 1

SELECT * FROM 
Skills LEFT OUTER JOIN 
(select distinct user_id, skill_id from User_Skills where user_id = 1001) xxx
    ON Skills.id = xxx.Skill_id

does that produce what you need?