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.