Home > Enterprise >  Join 2 table not displaying data
Join 2 table not displaying data

Time:11-14

Currently trying to join 2 table (table process & table skill requirement) But when using group_concat, it wont display if data in the group concat column (Skill Requirement) is empty.

It there any other way or sql line that i missed?

My expected result & current result is as below:

Expected Result:

Product Process Skill Requirement
133 Process1 skill1, skill2, skill3
133 Process2 skill1, skill4
133 Process3 skill1, skill2
133 Process4

Current Result

Product Process Skill Requirement
133 Process1 skill1, skill2, skill3
133 Process2 skill1, skill4
133 Process3 skill1, skill2

This is my SQL :

SELECT process.product_id,
process.process_id,
skillrequirement.process_id, GROUP_CONCAT(skillrequirement.skill_req) as skill_req,
process.process
FROM process 
JOIN skillrequirement 
ON process.process_id = skillrequirement.process_id 
WHERE process.product_id ='133'
GROUP BY process.process

Update:

Sample input data Table 1 : Process

process_id Process product_id
1 Process1 133
2 Process2 133
3 Process3 133
4 Process4 133

Table 2 : skill requirement

skillreq_id process_id skill_requirement
1 1 skill1
2 1 skill2
3 2 skill2
4 3 skill3
5 1 skill3
6 3 skill2

CodePudding user response:

Your sample data doesn't really match your expected outcome (for example, you are expecting a skill4 for process 2, but your sample data doesn't contain this). Furthermore, the query in your question is not valid and can't be executed. Anyway, as I already assumed in my comment, the main problem is that you need a LEFT JOIN instead of an INNER JOIN in order to also show processes without skills. This query produces the expected outcome if the sample data allows it:

SELECT p.product_id,
p.process,
GROUP_CONCAT(s.skill_req) AS skill_req
FROM process p
LEFT JOIN skillrequirement s -- Here you need the LEFT JOIN
ON p.process_id = s.process_id 
WHERE p.product_id = 133 -- Remove the WHERE clause if not required
GROUP BY p.process;

A note: I don't know if this is required for you, but if you want to make sure that the skills in the list are always sorted, you can add an ORDER BY clause to the GROUP_CONCAT part:

SELECT p.product_id,
p.process,
GROUP_CONCAT(s.skill_req ORDER BY s.skill_req) AS skill_req
FROM process p
LEFT JOIN skillrequirement s
ON p.process_id = s.process_id 
WHERE p.product_id = 133
GROUP BY p.process;

You can verify this here: db<>fiddle and see the differences.

  • Related