Rei Rei - 1 year ago 73
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 Source

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;


Festival: 2 order
Music: 0 order

SQL Fiddle:!9/006c09/4

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download