CODEWITHSUNDEEP
mysqlsqldatabase
Atul
Atul

Reputation: 4320

SELECT query with exclusions specified in other table - 1

Background:

Table A has columns userid, friend_id. It stores all friend_ids of user having userid.

e.g. Table A

-----------------------
| userid | friend_id  |
|---------------------|
| 3      |    5       |
| 3      |    6       |
| 3      |    7       |
| 3      |    8       |
| 3      |    9       |
-----------------------

Table B has columns blocker_userid, blocked_userid

e.g. Table B

-------------------------------------
| blocker_userid | blocked_userid   |
|-----------------------------------|
|       6        |      3           |
|       9        |      3           |
-------------------------------------

Problem Statement:

I want to get from table A friends list for given userid, except those friends who have blocked the userid. In case of above example, the query should return all friend_ids from table A for userid 3 except 6 and 9 (because they have blocked userid 3)

Can someone please tell how this can be done with single SELECT query?

Current Solution:

Currently I am firing multiple queries. One to get all friend_ids for given userid. And then query to table B with each friend_id to find if they have blocked the userid. This approach is not at all efficient.

Update:

wewesthemenace's solution below works perfect here. However, in case if we want to not to get "those who have blocked userid as well as those who have been blocked by userid" then here is solution (By Hanno Binder who has answered it here)

SELECT f.*
FROM friends f
LEFT OUTER JOIN blocks b1
    ON b1.blocker_userid = f.friend_id AND b1.blocked_userid = f.userid -- userid blocked by friend
LEFT OUTER JOIN blocks b2
    ON b2.blocker_userid = f.userid AND b2.blocked_userid = f.friend_id -- friend blocked by userid
WHERE b1.blocker_userid IS NULL
AND b2.blocker_userid IS NULL

Upvotes: 1

Views: 79

Answers (3)

Felix Pamittan
Felix Pamittan

Reputation: 31879

Using NOT EXISTS:

SELECT a.*
FROM TableA a
WHERE
    a.userid = @userid
    AND NOT EXISTS(
        SELECT 1 
        FROM TableB b
        WHERE
            b.blocked_userid = a.userid
            AND b.blocker_userid = a.friend_id
    )

SQL Fiddle

Using LEFT JOIN:

SELECT a.*
FROM TableA a
LEFT JOIN TableB b
    ON b.blocked_userid = a.userid
    AND b.blocker_userid = a.friend_id
WHERE
    a.userid = @userid
    AND b.blocked_userid IS NULL

Upvotes: 2

Jasqlg
Jasqlg

Reputation: 183

select friend_id,friend_name from tableA a 
Left Join tableB b on a.friend_id=b.blocker_userid and b.blocked_userid = @userid
where a.userid = @userid and b.blocker_userid is null

Upvotes: 1

Chirag Shah
Chirag Shah

Reputation: 1474

Using single query

select a.friend_name from table A as b, Table B as b where friend_id not In(blocker_userid) where userid = 3

Upvotes: -1

Related Questions