MySQL - WHERE/AND with pivot table

Question

I have 3 tables:

CLINICS

id | name | description | lat | lng | opening_hours| logo | address | city | zip | phone_number | email | url | gmaps_link | marker | created_at | updated_at

SERVICES

id | name | created_at | updated_at

CLINIC_SERVICES

clinic_id | service_id

This query should return two results, but at the moment it is returning 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

If I put OR instead of AND I am getting 5 clinics, which actually works as I think should work. How can I get proper result (clinic have both services)? What am I doing wrong here?

Have you check the supposed answer? Is the issue still relevant? — AlexL, Sep 06 '18 at 11:24

AlexL · Answer 1 · 2018-08-30T05:33:23.597

According to previous similar question this should work:

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

MySQL - WHERE/AND with pivot table

1 Answers1