Geek asks and answers

LEFT JOIN Table A Depends on Table B

I have a block user system:

Table a:

-------------------
id_user | username 
-------------------
   1    |    A
-------------------
   2    |    B
-------------------
   3    |    C
-------------------
   4    |    D
-------------------

.... and so on

Table B:

--------------------------------------
id_block | user_request | user_banned 
--------------------------------------
   1    |       1       |      2
--------------------------------------
   2    |       1       |      3
--------------------------------------
... and so on

Case # 1: When user A gets a list of users from table A, so user B hidden from him and user C.

Case # 2: When user B gets the list of users from table A, so user A is hidden from him.

Case # 3: When user C gets a list of users from table A, so user A is hidden from him.

Case # 4: When user D gets the list of users from table A, so he gets all the users.

So far I have tried this:

SELECT t1.id_user, t1.username

FROM user t1

  LEFT JOIN block_user t2

    ON (t2.user_request = 1 AND t2.user_banned = 1)

WHERE t1.id_user NOT IN

    (SELECT user_request FROM block_user WHERE user_request = 1 )

  AND t1.id_user NOT IN 

    (SELECT user_banned FROM block_user WHERE user_banned = 1)

As a result, User B and C are hidden! Big!

, 2, C. , A!

4, A, B C.

:

sqlfiddle 1

sqlfiddle 2

sqlfiddle 3

sqlfiddle 4

P.D. , , , .

+3
1
SELECT t1.id_user, t1.username
FROM user t1
LEFT JOIN block_user t2
ON 
   (t2.user_request = <current user id> AND t2.user_banned = t1.id_user)
   OR
   (t2.user_request = t1.id_user AND t2.user_banned = <current user id>)
WHERE t2.id_block IS NULL;

A, B, C D

+2

More articles:


All Articles