将两个表与mysql中的一组条件结合起来
问题描述:
我有三张表(doctor,clinic_doctor_timings,hosptial_doctor_timings)。时间表中包含医生表的ID作为外键,还存储医生的时间和时间。现在我需要选择在给定时间内去往诊所或医院的医生ID,即从和到。将两个表与mysql中的一组条件结合起来
我写了一个查询,可用于诊所或医院,但不是两者兼而有之。
SELECT
DISTINCT
SQL_CALC_FOUND_ROWS
`doctor`.`id`,
`doctor`.`name`
FROM
`doctor`
JOIN clinic_doctor_timings AS cl
ON cl.doctor_id = doctor.id
WHERE
TIME_FORMAT(`cl`.`from`, %h:%i %p) >= '05:00 AM'
AND TIME_FORMAT(`cl`.`to`, %h:%i %p) >= '10:00 PM'
上述查询工作正常,但之后,我想无论是clinic_doctor_timings和hospital_doctor_timings表结合我没有得到结果我的期望。
.........
JOIN clinic_doctor_timings AS cl
ON cl.doctor_id = doctor.id
JOIN hospital_doctor_timings AS hs
ON hs.doctor_id = doctor.id
WHERE
(TIME_FORMAT(`cl`.`from`, %h:%i %p) >= '05:00 AM'
AND TIME_FORMAT(`cl`.`to`, %h:%i %p) <= '10:00 PM')
XOR
(TIME_FORMAT(`hs`.`from`, %h:%i %p) >= '05:00 AM'
AND TIME_FORMAT(`hs`.`to`, %h:%i %p) <= '10:00 PM')
我需要为诊所和医院提供诊所和医院以外的给定时间。
答
获取谁去诊所医生:
SELECT a.id, a.name FROM doctor a
INNER JOIN clinic_doctor_timings b ON a.id = b.doctor_id
WHERE
b.`from` >= '2013-05-02 05:00:00' AND
b.`to` <= '2013-05-02 22:00:00';
获取谁去医院医生:
SELECT a.id, a.name FROM doctor a
INNER JOIN hospital_doctor_timings b ON a.id = b.doctor_id
WHERE
b.`from` >= '2013-05-02 05:00:00' AND
b.`to` <= '2013-05-02 22:00:00';
获取谁去诊所和医院
SELECT a.id, a.name FROM doctor a
INNER JOIN clinic_doctor_timings b ON a.id = b.doctor_id
INNER JOIN hospital_doctor_timings c ON a.id = c.doctor_id
WHERE
b.`from` >= '2013-05-02 05:00:00' AND
b.`to` <= '2013-05-02 22:00:00' AND
c.`from` >= '2013-05-02 05:00:00' AND
c.`to` <= '2013-05-02 22:00:00';
医生得到去诊所的医生或医院
SELECT a.id, a.name FROM doctor a
WHERE
(
SELECT COUNT(b.id) FROM clinic_doctor_timings b WHERE
b.doctor_id = a.id and
b.`from` >= '2013-05-02 05:00:00' AND
b.`to` <= '2013-05-02 22:00:00'
) > 0
or
(
SELECT COUNT(c.id) FROM hospital_doctor_timings c WHERE
c.doctor_id = a.id and
c.`from` >= '2013-05-02 05:00:00' AND
c.`to` <= '2013-05-02 22:00:00'
) > 0;
这还不够得到你想要的吗?
我还是不明白你想要什么.... – mjb 2013-05-02 12:33:21