Using count from subquery to determine if row should be included in result rows

Question

I am building a website and I need a query to return users based on proximity using gps coordinates, but also only if the user also has a photo that is marked private. The photos table is called 'photos' and the identifier to whom the photo belongs to is the user_id column. The same column name also exists in the users table for each user. What I am trying to do is use a subquery to see if the user has any photos marked private (private = 1) in the photos table, and if so, return that user and some basic info.

For some reason my query won't work and I have been struggling with this for quite some time. Any help would be greatly appreciated!

Here is the query that I've built which doesn't work. I've tried many other queries as well.

SELECT users.user_id, 
       users.display, 
       users.lat, 
       users.lng, 
       users.featured, 
       profile.type, 
       profile.user_id, 
       ( 3959 * Acos(Cos(Radians('41.6032207')) * Cos(Radians(users.lat)) * Cos( 
                                   Radians(users.lng) - Radians('-73.087749')) + 
                              Sin(Radians('41.6032207')) * Sin( 
                              Radians(users.lat))) ) 
                              AS distance, 
       Count(photos.photo_id) 'Photo Count' 
FROM   users, 
       profile 
       INNER JOIN photos 
               ON users.user_id = photos.user_id 
                  AND photos.private = 1 
GROUP  BY users.user_id 
HAVING Count(photos.photo_id) > 0 
       AND users.account_type = '1' 
       AND profile.user_id = users.user_id 
       AND users.active = '1' 
       AND distance <= '10' 

Answer 1

You should not use mixed (explicit and implicit) join syntax. You should use only explicit join syntax and move the join condition from the having to the where clause:

  SELECT users.user_id, 
         users.display, 
         users.lat, 
         users.lng, 
         users.featured, 
         profile.type, 
         profile.user_id, 
         ( 3959 * Acos(Cos(Radians('41.6032207')) * Cos(Radians(users.lat)) * Cos( 
                                     Radians(users.lng) - Radians('-73.087749')) + 
                                Sin(Radians('41.6032207')) * Sin( 
                                Radians(users.lat))) ) 
                                AS distance, 
         Count(photos.photo_id) `Photo Count`
  FROM   users
  INNER JOIN profile ON profile.user_id = users.user_id 
  INNER JOIN photos ON users.user_id = photos.user_id 
      AND photos.private = 1 
  WHERE users.account_type = '1' 
    AND users.active = '1' 
  GROUP  BY users.user_id 
  HAVING Count(photos.photo_id) > 0 
    AND distance <= '10' 

and you should use backticks for a composite column name (not single quote)