使用php中的join计算tbl_appointment_book中的client_id

Question

我有两张表。我想要来自tbl_appointment_book的count client_id，但tbl_appointment_book的id和tbl_appointment_service的appointment_id应该匹配。

tbl_appointment_book

id appointment_date client_id status
1  2016-05-11         1         1
2  2016-05-12         1         1

tbl_appointment_service

id appointment_id service_id team_id
1   1               1          1
2   1               2          1
3   1               8          5
4   2               1          1
5   2               1          2

我想从tbl_appointment_book条件计数client_idappointment_date=2016-05-11, team_id=1

Answer 1

您可以使用JOIN来实现您想要的结果：

SELECT count(a.client_id) FROM tbl_appointment_book a
                          JOIN tbl_appointment_service b
                              ON a.id = b.appointment_id
                          WHERE a.appointment_date = '2016-05-11' AND b.team_id = 1

结果将是返回的行数。

Answer 2

尝试下面的查询，它应该可以正常工作。

SELECT COUNT (tab.client_id)
FROM tbl_appointment_book tab
JOIN tbl_appointment_service tas
ON tab.id = tas.appointment_id
WHERE tab.appointment_date = '2016-05-11' AND tas.team_id = 1;