MySQL query doesn't show results properly

Question

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

id         name     longitude       latitutde  
-----      -----    -----------    -----------
1          Mark     -82.347036     29.6545095
2          John     -82.357036     29.665095
3          Paul     -82.367036     29.645095
4          Dave     -82.337036     29.675095
5          Chris    -82.437036     29.575095
6          Manny    -82.538036     29.745095
7          Fred     -82.638036     29.346095

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
    FROM `users` HAVING distance < 50 ORDER BY distance DESC LIMIT 20`

The other matches is like this:

id         sender    receiver        status  
-----      -----    -----------    -----------
1            3           4              0
2            1           5              1
3            6           3              1
4            2           6              0
5            2           1              0

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

My Query:

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 <> 1 
    HAVING distance < 5000 
    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

My fiddle: https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=95972531a390a013796ed4cf7ad99884

In matches table Id 1 have match only with Id 5 (where status is 1).

The part with:

       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 )

is wrong somewhere, because it has to show id 2,3,4,6,7 not only 6 & 7 . Check out last query in fiddle

The query must be with logic:

If I am the sender with id 1 don't return me receiver(a.id).

If I'm receiver with id 1 return me sender(a.id) where status = 0.

If I'm not sender or receiver return me a.id

Answer 1

When I use your logic

You want:

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 <> 1
    HAVING distance < 5000
    ORDER BY distance DESC
    LIMIT 20) a
WHERE
    a.id NOT IN (SELECT `sender` FROM (SELECT `sender` FROM matches UNION  SELECT `receiver` FROM matches) t1
WHERE `sender` IN (SELECT `sender` FROM matches WHERE `receiver` = 1 AND status = 1
UNION SELECT `receiver` FROM matches WHERE `sender`  = 1
))
ORDER BY a.distance ASC

id |           distance
:- | -----------------:
3  | 2.8991986256467865
2  |  2.946298180421104
4  |  3.335840468953696
7  |  44.20441197234838

db<>fiddle here

Answer 2

   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 )

The first part of the OR gets ids senders, hence ids, 1 and 6.

But the second part is even more incriminating.

SELECT `sender` FROM matches    -- 1,2,2,3,    6
UNION ALL 
SELECT `receiver` FROM matches  -- 1,    3,4,5,6

The UNION ALL is 1,1,2,2,3,3,4,5,6,6.

The dups are ignored.

Meanwhile, a.id is some subset of 1,2,3,4,5,6,7, depending on the distance arithmetic. Hence the only a.id left from the second part of the OR is 7.

So, I would expect to get 1, 6, and maybe 7.

Hmmm... I think I am getting lost, and I think you were getting lost. Please change things so that there are not two ids, and 4 meanings of 1. 1 as a status, 1 as a sender/receiver, 1 as two different ids. For example, make the matches.id between 11 and 16; make all the senders/receivers between 21 and 27. Make the statuses ON and OFF (or something).