How to create a MySQL view

Question

I have MySQL visits table as

visit_date        client_id
2019-01-01 08-00-00 1
2019-01-01 11-00-00 2

work_hours table as

hour    name
8        '08-00'
9        '09-00'
...
18        '18-00'

and tables clients as

  client_id client_name
  1           aaa
  2           bbb 
  3           ccc

How to create a view to get data?

client_id   hour_name    count
1        '08-00'    1
1        '09-00'    0   
.....
1        '18-00'    0
2        '08-00'    0
2        '09-00'    0
2        '10-00'    0
2        '11-00'    1
2        '12-00'    0
.....
2        '18-00'    0
3        '08-00'    0
3        '09-00'    0
.....    all zero
3        '18-00'    0

Answer 1

Try this:

SELECT c.client_id, w.hour_name,
    (SELECT COUNT(1) 
    FROM visits v
    WHERE v.client_id = c.client_id
    AND HOUR(v.visit_date) = w.hour) as tot_visit
FROM clients c
CROSS JOIN work_hours w
ORDER BY c.client_id, w.hour_name

See SQL Fiddle