sql - MySQL request for combining and searching in 2 tables

Question

I have 2 tables. One of my tables with users is:

id         name     longitude       latitutde  
-----      -----    -----------    -----------
1          Mark     -76.316528     40.036027
2          John     -95.995102     41.25716
3          Paul     -82.337036     29.645095
4          Dave     -82.337036     29.645095
5          Chris    -76.316528     40.036027

I'm using SQL to detect nearby people: SELECT id, ( 3959 * acos( cos( radians(37) ) * cos( radians( latitude ) ) * cos( radians( longitude ) - radians(-122) ) + sin( radians(37) ) * sin(radians(latitude)) ) ) AS distance FROMusersHAVING distance < 50 ORDER BY distance DESC LIMIT 20

The other matches is like this:

id         sender    receiver        status  
-----      -----    -----------    -----------

Where sender is the person who sent the invitation receiver is the person who receives is.

The SQL request should search in users for near by people and check in the matches for their ids. If the ids are not there as sender & receiver return the userID of the person to make a new connection.

For example Paul is searching for nearby people and Dave as we see from users is nearby him. So Dave ID should be returned to Paul ONLY , if their ids are not in matches table OR Paul == receiver AND status == 0.

How this SQL Query should be written to return 20 Ids of the nearby people ?

Answer 1

I change the distance formula to something that works since mysql 5.7. You didn't_ specify your database or presented a fiddle example to show your system, so i selected 5.7 as mini9mum and it works also in 8.0 as you see in the fiddle.

the inner query needs two things the user id who searches and his position, because he is excluded from the results and of course to calculate the distance.

The result from the query is checked in the where clause-I hope i got the right idea, so you should check that

CREATE TABLE users
    (`id` varchar(5), `name` varchar(5), `longitude` varchar(11), `latitude` varchar(11))
;

INSERT INTO users
    (`id`, `name`, `longitude`, `latitude`)
VALUES
    ('1', 'Mark', '-76.316528', '40.036027'),
    ('2', 'John', '-95.995102', '41.25716'),
    ('3', 'Paul', '-82.337036', '29.645095'),
    ('4', 'Dave', '-82.337036', '29.645095'),
    ('5', 'Chris', '-76.316528', '40.036027'),
    ('6', 'Manny', '-82.338036', '29.645095'),
    ('7', 'Fred', '-82.338036', '29.646095')
;

?

?

CREATE TABLE matches
    (`id` int, `sender` int, `receiver` int, `status` int)
;

INSERT INTO matches
    (`id`, `sender`, `receiver`, `status`)
VALUES
    (1, 3, 4, 0),
    (2, 1, 5, 1),
    (3, 6, 3, 1)
;

?

?

SELECT 
  id
  , ( 3959 * acos( cos( radians(37) ) * cos( radians( latitude ) ) 
     * cos( radians( longitude ) 
     - radians(-122) ) + sin( radians(37) ) 
     * sin(radians(latitude)) ) ) AS distance 
FROM
    users
HAVING distance < 50 
ORDER BY distance 
DESC LIMIT 20

id | distance
:- | -------:

SELECT
a.id
,a.distance
FROM
  (Select  
       id,
       st_distance_sphere(POINT(-82.337036, 29.645095 ), POINT(`longitude`, `latitude` ))/1000  as distance
    FROM
        users u  
    WHERE id <> 3 
    HAVING distance < 50 
    ORDER BY distance 
    DESC LIMIT 20) a
WHERE
    a.id in (SELECT `sender` FROM matches WHERE status = 1)
    OR a.id NOT IN ( SELECT `sender` FROM matches UNION ALL SELECT `receiver` FROM matches )
ORDEr BY a.distance ASC

id |            distance
:- | ------------------:
6  | 0.09663995445895139
7  | 0.14732089261518266

db<>fiddle here