MySQL: Don't select records matched in other join

Question

Maybe my title is wrong but I want to show you my query and what's the error I have in my result to perform a search:

Query

select distinct
    U.id,
    U.first_name,
    U.last_name,
    C.from_user,
    case when C.to_user is null 
        then 99
        else C.to_user
    end as to_user,
   case when C.status is null 
        then 99
        else C.status
    end as connection_type,
    case when C2.from_user is null 
        then 99
        else C2.from_user
    end as from_user_2,
    C2.to_user as to_user_2,
    case when C2.status is null 
        then 99
        else C2.status
    end as connection_type_2,
    ( 3959 * acos(
        cos(radians(19.3901580))
        * cos(radians(L.latitude))
                * cos(radians(L.longitude) - radians(-99.1733260))
                + sin(radians(19.3901580))
                * sin(radians(L.latitude)))
    ) AS distance
from users U
left join connections C on C.from_user = U.id
left join connections C2 on C2.to_user = U.id
left join locations L on L.user_id = U.id
where U.id != 10
#group by U.id
having distance < 70
    #and (connection_type_2 = 1 or connection_type_2 = 99)
   #and from_user_2 != 10
   #and (to_user != 10 or connection_type != 3)
   #and to_user != 10
order by distance asc

Result

If I uncomment the lines commented in the query, this is the result

user id: 10

If the to_user = 10 and with the from_user id the connection_type = 3 I need to don't get the user which already matches with this kind of condition and that means, the user which have the id 30 won't be selected.

So I have that issue, are many rules for this search function of course but this is the last step to get it done!

EDIT:

A bit explanation of the requests

• A users connected is when the status between them is 3
• A incoming request is when the status between them is 1 like:
  • from_user:20 to_user:10 status:1 (the user 10 has a incoming request from 20
  • from_user:10 to_user:20 status:1 (the user 20 has a incoming request from 10
• A outgoing request is when the status between them is 1 like:
  • from_user:20 to_user:10 status:1 (the user 10 has sent a request to 20
  • from_user:10 to_user:20 status:1 (the user 20 has sent a request to 10
• A blocked user is when the status from_user to_user is 2:
  • from_user:20 to_user:30 status:2 (the user 20 blocked the user 30)

Rules

Get:

• I need to get the users which doesn't have any requests, it means users which are not in the connections table
• Get the users which has sent to the current user (10 in this case) a request.

Don't get:

• Don't get users which I already sent an invitation to them
• Don't get users blocked

Hope you can help me!

Answer 1

You can try this

select * from (
   select id, first_name, last_name, (select  3959 * acos(
        cos(radians(19.3901580))
        * cos(radians(L.latitude))
                * cos(radians(L.longitude) - radians(-99.1733260))
                + sin(radians(19.3901580))
                * sin(radians(L.latitude)))
                from locations l where l.userid = u.id ) as distance  from  users u
  where 
  (
   id in (select from_user from connections where to_user=10 and status=1)
    or (id not in (select from_user from connections where to_user=10) and id not in (select to_user from connections where from_user=10) )
  ) 
  and id !=10
  and id not in (select to_user from connections where from_user =10 and status=2)
  and id not in (select to_user from connections where from_user=10 and status=1) 
  and id not in (select to_user from connections where from_user=10 and status=3)
  and id not in (select from_user from connections where to_user=10 and status=3)
) a
where distance < 10;

It's not optimized yet but you need get the correct results first.

To add the status of connections with the current user

select *,
   (select status from connections l where (l.to_user=a.id
          and l.from_user = 10
            or l.from_user = a.id  and l.to_user=10 
     limit 1)  as status
  from (
   select id, first_name, last_name, (select  3959 * acos(
        cos(radians(19.3901580))
        * cos(radians(L.latitude))
                * cos(radians(L.longitude) - radians(-99.1733260))
                + sin(radians(19.3901580))
                * sin(radians(L.latitude)))
                from locations l where l.userid = u.id ) as distance  from  users u
  where 
  (
   id in (select from_user from connections where to_user=10 and status=1)
    or (id not in (select from_user from connections where to_user=10) and id not in (select to_user from connections where from_user=10) )
  ) 
  and id !=10
  and id not in (select to_user from connections where from_user =10 and status in (1,2,3)) 
  and id not in (select from_user from connections where to_user=10 and status=3)
) a
where distance < 10