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

Because it's the logic you put in your WHERE statement. The statement a.id in (SELECT sender` FROM matches WHERE status = 1)` will only return id 1 and 6. That immediately filter out the rest. Then in the outer query, you put another filter WHERE id <> 1. So it just leaves with id 6. — Eric
– Eric, Commented Apr 29, 2020 at 18:45
It's not an answer. I am just telling you why id 2, 3, 4, 5 is missing. You don't tell us what logic you want, so no one is able to help you. — Eric
– Eric, Commented Apr 29, 2020 at 21:00

GMB · Accepted Answer · 2020-05-02 00:21:27Z

2

+300

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

edited May 2, 2020 at 0:21

GMB

224k25 gold badges103 silver badges151 bronze badges

answered Apr 30, 2020 at 19:52

nbk

49.6k8 gold badges32 silver badges53 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Bogdan Bogdanov Over a year ago

dbfiddle.uk/… check out this. I've inserted in matches (6, 6, 1, 1) . Why I get id 6 ? It shouldn't return me id 6, if status is greater than 0

nbk Over a year ago

you have no rule for that. in the logic you presented, but i also excluded every SENDER where 1 is the receiver and has status = 1

Rick James · Accepted Answer · 2020-05-01 00:24:15Z

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

Collectives™ on Stack Overflow

MySQL query doesn't show results properly

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related