MySQL Select from multiple table with HAVING clause

Question

I have a small database which consists of a couple of tables that I have set out below:

Users

| Name         | Type              | Extra        |      
| id           | int(10)           | Auto Increment    
| emailAddress | varchar(150)                          
| socialNetwrk | enum('G','F','T')                     
| photoUrl     | varchar(255)                          
| regLat       | decimal(10, 8)                        
| regLng       | decimal(11, 8)                        
| regDate      | timestamp                             
| active       | enum('1','0')     | default = 1    

locations

| Name      | Type          | Extra                       
| id        | int(10)       | Auto Increment    
| userID    | int(10)       | Foreign Key (users.id)    
| lat       | decimal(10, 8)|     
| lng       | decimal(11, 8)|    
| timestamp | timestamp     | CURRENT_TIMESTAMP

I am using the following query in order to return all nearby users (excluding the current user) that have an active account:

$result = $mysqli->query("
  SELECT DISTINCT l.userID
              , u.active
              , (3959 * acos( cos( radians($lat) ) 
                            * cos( radians( lat) ) 
                            * cos( radians( lng) - radians($lng) ) 
                            + sin( radians($lat) ) 
                            * sin( radians( lat )) 
                            ) 
                ) distance 
           FROM locations l
           JOIN users u 
           HAVING distance < 30 
           AND userID != $cur_user_id 
           AND active = 1 
           ORDER 
           BY RAND()
")or die($mysqli->error);

I get back results without errors however I can't get it to include only active accounts (with a tag of 0 instead of 1).

Answer 1

Maybe something like this?

SELECT DISTINCT l.userID
              , u.active
              , (3959 * acos( cos( radians($lat) ) 
                            * cos( radians( lat) ) 
                            * cos( radians( lng) - radians($lng) ) 
                            + sin( radians($lat) ) 
                            * sin( radians( lat )) 
                            ) 
                ) distance 
           FROM locations l
           INNER JOIN users u ON l.userID = u.id
           WHERE distance < 30 
           AND userID != $cur_user_id 
           AND active = 1 
           ORDER BY RAND()