我的预约系统有一些经理(例如医生),我希望所有的预约时间都显示在一个表中,这样预约就可以用用户的名字来填充或清空创建时间预约表:
+-----------+-----------+
| id | time
+-----------+------------
| 1 | 10:00 |
| 2 | 10:30 |
| 3 | 11:00 |
| 4 | 11:30 |
| 5 | 12:00 |
| 6 | 12:30 |
| 7 | 13:00 |
| 8 | 13:30 |
| 9 | 14:00 |
+-----------+-----------+
管理员表
+-----------+-------------+-----------+
| id | name | phone |
+-----------+-------------+-----------+
| 1 | alex | 123456 |
| 2 | dany | 123456 |
用户表:
+----+------+-------+-----------+
| id | manager_id | name | phone |
+----+------------+------+-----------+
| 1 | 1 | John | 123456 |
| 2 | 1 | Sara | 123456 |
| 3 | 2 | lorem | 123456 |
+----+------+-------+-----------+
和订单表(用于预约)
+----+------+-------+------------------+-----------+
| id | manager_id | userid | reserveid| datereserve
+----+------------+------+-------------+-----------+
| 1 | 1 | 1 | 1 |2016/12/28
| 2 | 1 | 2 | 2 |2016/12/28
| 3 | 2 | 3 | 3 |2016/12/28
+----+------+-------+-----------+-----------+------+
现在,我想在一个表中显示预约时间,在该表中,用用户名称填充或清空预约比如:类似于此照片
我加入了查询中的所有表,但无法显示类似的照片和获取数据:(我该如何实现它?
SELECT
m.name,
a.time,
u.phone,
u.name <- if u.name or u.phone is NULL, then appointment is free now
FROM manager as m
CROSS JOIN appointments as a
LEFT JOIN order as o ON o.manager_id = m.id AND o.appointmentsid = a.id
LEFT JOIN user as u ON u.id = o.userid
#for some doctor
WHERE m.id = DoctorID
我建议换一张表
create table status(id int, name varchar(50));
并插入值(1,"保留"),(2,"自由")。
select t.time, s.name, m.name, u.name, u.phone
from time t left join status s on t.id = s.id
left join order o on o.appointmentsid = t.id
left join manager m on o.manager_id = m.id
left join users u on o.userid = u.id;