Search code examples
mysqlsqlleft-joinhaving-clause

MySQL: Using JOINS with WHERE clause to get all records from one table and available records from the other

I am tryin to run a SQL query as below

SELECT organization.idorganization,
organization.name,
organization.image_url,
organization.website,
organization.description,
organization.phone,
organization.email,
organization.spare_parts_items,
organization.specialty_vehicles,
organization.on_the_spot_service,
organization.home_visits,
organization.latitude,
organization.longitude,
organization.no_of_views,
 organization.is_disabled,
organization.date_created,
organization.last_update,
organization.address,
cover_photo.idcover_photo,
cover_photo.image_url,
cover_photo.thumbnail,
( 6371 * acos( cos( radians(7.294324) ) * cos( radians( organization.latitude ) ) * cos( radians( organization.longitude ) - radians(80.646185) ) 
+ sin( radians(7.294324) ) * sin( radians( organization.latitude ) ) ) ) AS distance 
FROM organization 
LEFT OUTER JOIN cover_photo ON cover_photo.idcover_photo = organization.idorganization
WHERE organization.idorganization_type =1 HAVING distance < 20 AND cover_photo.thumbnail=true

Here, my expectation is to get all records from organization whether it has cover_photos or not. Thats why I have used a LEFT OUTER JOIN. But, if it has cover_photos, it should get only those which is a thumbnail. The cover_photo contains more than one photo for each organization.

When I run my code, I do not get what I am looking for. If there is no thumbnails available in cover_photo table, that particular organization is completely ignored. At this moment I have no cover_photo for any organization, so what I get is no results.

How to fix this?

Solution

  • Basically, you need to move the condition on the LEFT JOINed table from the WHERE clause to the ON clause of the LEFT JOIN: otherwise, it becomes mandatory, and filters out rows where the LEFT JOIN did not match.

    That is, change this:

    LEFT OUTER JOIN cover_photo 
    ON  cover_photo.idcover_photo = organization.idorganization
WHERE organization.idorganization_type = 1 AND cover_photo.thumbnail = true
HAVING distance < 20

    To:

    LEFT OUTER JOIN cover_photo 
    ON  cover_photo.idcover_photo = organization.idorganization
    AND cover_photo.thumbnail = true
WHERE organization.idorganization_type = 1 
HAVING distance < 20