Join 2 table not displaying data

Question

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

It seems for me that you just need to replace "JOIN" with "LEFT JOIN", but to be sure, you need to show us the sample input data of both tables. — Jonas Metzler, Nov 11 '22 at 06:00
hi @JonasMetzler , i've updated my question with sample input both table. thankyou — newbieFAR, Nov 14 '22 at 03:49

Jonas Metzler · Accepted Answer · 2022-11-14T04:32:44.140

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.

Yes you are right, the left join is what I needed. Thanks for the info. I learn something new today. :) — newbieFAR, Nov 14 '22 at 05:48

Join 2 table not displaying data

1 Answers1