Rei Rei - 6 months ago 8
SQL Question

How to know count data and show in multiple table

I have some problem with my query.

This is my first table, "order":

id_order id_event
------------------
1 12
2 12


This is my second table, "event_table":

id_event event_name id_eo
--------------------------------
12 Festival 1
13 Music 1


All I want is the result like this:

Festival : 2 order
Music : 0 order


This is what I have been done so far:

select (select count(*) from order) as jumorder,
event_name from order p
INNER JOIN event ON(p.id_event=event.id_event)
WHERE event.id_eo='1'

Answer

Using LEFT JOIN and CONCAT you can get the expected result as mentioned in the post.

SELECT CONCAT(E.event_name, ": ", COUNT(O.id_event), " order") Result
FROM `event_table` E
LEFT JOIN `order` O ON O.id_event = E.id_event
WHERE id_eo = '1'
GROUP BY E.event_name;

Result

Festival: 2 order
Music: 0 order

SQL Fiddle: http://sqlfiddle.com/#!9/006c09/4