我有三张table:
诊所
id | name | description | lat | lng | opening_hours| logo | address | city | zip | phone_number | email | url | gmaps_link | marker | created_at | updated_at
服务
id | name | created_at | updated_at
诊所服务
clinic_id | service_id
此查询应返回两个结果,但此时返回0:
SELECT * FROM
(SELECT clinics.id as cid, clinics.lat, clinics.lng, clinics.opening_hours,
clinics.logo, clinics.address, clinics.city, clinics.name, clinics.description,
clinics.zip, clinics.phone_number, clinics.email, clinics.url, clinics.gmaps_link,
clinics.marker,
countries.full_name AS country,
(6378 * acos(
cos(radians(-33.84801)) * cos(radians(lat)) *
cos(radians(lng) - radians(151.06488)) +
sin(radians(-33.84801)) * sin(radians(lat))))
AS distance
FROM clinics
JOIN countries ON countries.id = clinics.country_id
LEFT JOIN clinics_services ON clinics.id = clinics_services.clinic_id
WHERE clinics_services.service_id = 1 AND clinics_services.service_id = 29
GROUP BY clinics.id
) AS distances
WHERE distance < 50000
ORDER BY distance ASC
如果我把或代替,我得到5个诊所,这实际上是我认为应该工作。我怎样才能得到正确的结果(诊所有两种服务)?我做错什么了?
1条答案
按热度按时间yhuiod9q1#
根据前面的类似问题,这应该有效: