How to get data by using join in mysql

Question

I have a table:

enter image description here

and this table:

enter image description here

I would like to create report like this

enter image description here

I've tried this SQL:

select master_problem.problem, master_problem.sop_reference, master_problem.adidas_spec, count(log_roving_qc.id_problem) as jumlahfrom master_problem inner join log_roving_qc on master_problem.id_problem = log_roving_qc.id_problem group by master_problem.id_problem

but the empty data does not show. I want to display blank data with a description of 0

I have attempted to fix the problems with the query you actually showed us. If you want something other than this, then edit your question and make that more clear. — Tim Biegeleisen
– Tim Biegeleisen, Commented Dec 26, 2017 at 2:25

Tim Biegeleisen · Accepted Answer · 2017-12-26 02:23:28Z

Do a left join of the master_problem table to a subquery which does the count aggregation:

SELECT mp.problem, mp.sop_reference, mp.adidas_spec, COALESCE(t.cnt, 0) AS jumlahfrom FROM master_problem mp LEFT JOIN ( SELECT id_problem, COUNT(*) as cnt FROM log_roving_qc GROUP BY id_problem ) t ON mp.id_problem = t.id_problem;

Collectives™ on Stack Overflow

How to get data by using join in mysql

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related