MySQL - WHERE/AND 与数据透视表

Question

我有 3 张 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_SERVICES

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

如果我输入OR而不是AND，我会得到5个诊所，这实际上按照我认为应该的方式工作。我怎样才能得到正确的结果(诊所有这两种服务)？我在这里做错了什么？

Answer 1

根据previous similar question这应该有效:

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 OR clinics_services.service_id = 29
        GROUP BY clinics.id
        HAVING SUM(clinics_services.service_id = 1) > 0 AND SUM(clinics_services.service_id = 29) > 0
        ) AS distances
    WHERE distance < 50000
    ORDER BY distance ASC