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).

You don't put `userID != '$cur_user_id' AND active = '1'` in a `having` clause but in a `where` clause. — Daan, Oct 14 '14 at 13:48
Using a WHERE clause gives this response: Unknown column 'distance' in 'where clause' — jampez77, Oct 14 '14 at 13:52
As an aside - There is **no more support** for `mysql_*` functions, they are [**officially deprecated**](https://wiki.php.net/rfc/mysql_deprecation), **no longer maintained** and will be [**removed**](http://php.net/manual/en/function.mysql-connect.php#warning) in the future. You should update your code with [PDO](http://php.net/pdo) or [MySQLi](http://php.net/mysqli) to ensure the functionality of your project in the future. — misterManSam, Oct 14 '14 at 14:06
@Daan - You can use HAVING. It may be less efficient, but it saves some typing. — Strawberry, Oct 14 '14 at 14:22
@Strawberry The two tables are connected by a foreign key (users.id - locations.userID) — jampez77, Oct 14 '14 at 14:25
Perhaps you should make use of that fact within your query! ;-) Remember, it's Structured Query Language! — Strawberry, Oct 14 '14 at 14:30
I updated the code im using the mysqli, thank you @misterManSam — jampez77, Oct 14 '14 at 15:05

score 0 · Accepted Answer · answered Oct 14 '14 at 15:10

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()

This works after changing WHERE for HAVING. Thank you very much @Andrew — jampez77, Oct 14 '14 at 15:13

MySQL Select from multiple table with HAVING clause

1 Answers1