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 FROM
usersHAVING 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 ?
See Question&Answers more detail:os