CODEWITHSUNDEEP
mysqlsqljoin
Pavel Sarpov
Pavel Sarpov

Reputation: 73

How to make SQL JOIN with exclusion

I have 3 tables:

  1. users [id, name]
  2. blacklist [blockerid, blockedid]
  3. location [uuid, lat, lon]

I need to get all data from location table with condition that if the man who asks for location is presented in blacklist as blocker or blocked will not see these forrbidden location. E.g:

Blacklist:
10 11

Location
10 74.1231 51.12312
11 82.1231 -1.31241
12 10.2121 34.12312

if 12 asks for location he gets all. if 11 asks for location he will got only location of 12, the same as 10.

Need help in request

Upvotes: 1

Views: 163

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269563

I would strongly recommend phasing the query as:

select l.*
from location l
where not exists (select 1 from blocklist bl where bl.blockerid = l.uuid and bl.blockedid = ?) and
      not exists (select 1 from blocklist bl where bl.blockedid = l.uuid and bl.blockerid = ?);

The ? is a placeholder for the user id that you care about.

This can take advantage of two indexes on blocklist, blocklist(blockerid, blockedid) and blocklist(blockedid, blockerid) for a significant performance gain.

Upvotes: 0

ScaisEdge
ScaisEdge

Reputation: 133360

You could using an union for get both blockerid and blockedid as id for a not in 

  select * 
  from localtion 
  where uuid not in ( 
        select blockerid as my_id
        from (
            select  blockerid,  blockedid
            from  Blacklist
            where   blockerid = your_id
            or   blockedid = your_id 
        ) AS alias
        union 
        select blockedid
        from (
            select  blockerid,  blockedid
            from  Blacklist
            where   blockerid = your_id
            or   blockedid = your_id 
        ) AS alias

  )

Upvotes: 1

Related Questions