CODEWITHSUNDEEP
mysql
Nairb
Nairb

Reputation: 13

Combine 3 tables and output a person, a skill and if the person has or does not have the skill

I'm new to mysql and stuck. I found similar questions but they did not answer my qeustion or I didn't understand them. I want to combine three tables, a person_table which has the person, a skill_table which has the skill and a person_skill table that has both the person and the skill. I want to output a name, a skill and a field that shows if the person has this skill and also if he does not have this skill.

I have the following 3 tables in my database:

person_table:

| id   | person_name | person_id |
|:----:|:-----------:|:---------:|
| 1    | user1       | 1         |       
| 2    | user2       | 2         |

skill_table:

|id      | skill_name | skill_id  | 
|:------:|:----------:|:---------:|
| 1      | skill1     | 3         
| 2      | skill2     | 4

person_skill_table:

| id       | person_id | skill_id |
| :------: | :-------: | :------: |
| 1        | 1         | 3        
| 2        | 1         | 4

I want a query that gives me this result, my query does not produce this:

| id       | person_name | skill_name  | excist | 
| :------: | :----------:| :---------: | :-----:|
| 1        | user1       | skill1      |  YES   
| 2        | user1       | skill2      |  YES   
| 3        | user2       | skill1      |  NO    
| 4        | user2       | skill2      |  NO

The query that I have:

SELECT person_table.person_name, skill_table.skill_name,
CASE WHEN (skill_table.skill_id = person_skill_table.skill_id AND
           person_table.person_id = person_skill_table.person_id ) 
           THEN "YES" 
           ELSE "NO" 
END AS excist 
FROM person_table 
JOIN skill_table 
JOIN person_skill_table 
GROUP BY person_table.person_name, skill_table.skill_name

My code produces this result:

| id       | person_name | skill_name  | excist | 
| :------: | :----------:| :---------: | :-----:|
| 1        | user1       | skill1      |  YES   
| 2        | user1       | skill2      |  NO   
| 3        | user2       | skill1      |  NO    
| 4        | user2       | skill2      |  NO

How can I achieve this?

Any help would be greatly appreciated.

Upvotes: 1

Views: 56

Answers (1)

Rahul Biswas
Rahul Biswas

Reputation: 3467

Please check this query.

-- MySQL (v5.8)
SELECT ROW_NUMBER() OVER (ORDER BY t.person_id, t.skill_id) id
     , t.person_name, t.skill_name
     , CASE WHEN pst.id IS NULL THEN 'NO' ELSE 'YES' END excist 
FROM (SELECT pt.person_id, pt.person_name
     , st.skill_id, st.skill_name
FROM person_table pt
CROSS JOIN skill_table st) t
LEFT JOIN person_skill_table pst
       ON t.person_id = pst.person_id
       AND t.skill_id = pst.skill_id;

Also check from url https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=5884e14ca3e42ebda8fd4942ff1f40ad

As skill table is base here so you can omit subquery for optimization purpose. Please check this query below.

SELECT
      ROW_NUMBER() OVER (ORDER BY pt.person_id, t.skill_id) id
      , pt.person_name
      , t.skill_name
      , CASE WHEN pst.id IS NULL THEN 'NO' ELSE 'YES' END excist 
      
FROM skill_table t
LEFT JOIN person_table pt ON t.department_id = pt.department_id
LEFT JOIN person_skill_table pst 
ON t.skill_id = pst.skill_id
AND pt.person_id = pst.person_id
WHERE t.department_id = 1 
    AND pt.department_id = 1;

Please check url https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=4e32edf8610fb55bfec553c54a8c5256

Upvotes: 1

Related Questions