Query between 3 tables with MySQL

I have tried to reproduce your problem based on real data and I have not found an optimal way to obtain the desired result.

If I understand correctly, it is about managing relationships between users.

The matches table would serve to know which users have requested friendship to which users. The accepted column would indicate if the friend request is accepted 1 or not accepted 0 .

The blocked table would serve to indicate who has blocked whom.

There are several problems in that design. I found a way to get the results you wanted, but it was not entirely satisfactory for just one reason: in the table matches the user can participate say as sender or as receiver em> of the friend request. In that case, check that the user we are looking for is sender or as receiver knocked down my supposed solution.

It seems to me that your data design can be thought of better. Also, you may not have to handle only the locks. What if you have to handle things like pending requests? What if you need to know who blocked whom or if they have blocked each other?

Then I propose this solution: create in the table matches a column that will be in charge of managing all the possible states of the relationship , namely:

• friends : both have accepted the request
• Receiver's pending sender : that is, so-and-so sent a friend request to Mengano, but he has not responded
• receiver pending issuer : that is, mengano sent request to so and so, but he has not responded
• so-and-so blocked to mengano
• mengano blocked so-and-so
• they blocked each other : that is, the friendship relationship ended in tragedy):

In your design you can implement a numerical convention (see comment before the% CREATE TABLE in the DEMO below) that expresses each state of the relationship or you can use ENUM .. That's the least of it.

By doing so, through this query you will get for example all the friends of the user 1 :

SELECT 
    ue.user_id id_emisor, 
    ur.user_id id_receptor
FROM matches_20180713 m
    LEFT JOIN users_20180713 ue ON ue.user_id=m.match_emisor
    LEFT JOIN users_20180713 ur ON m.match_receptor=ur.user_id
WHERE 
    m.match_emisor=1 OR m.match_receptor=1 AND m.match_status=1;

The result would be something like this:

id_emisor   id_receptor
    1            2
    4            1

Shows that the user 1 is friend of the users 2 and 4 . And it also shows that he asked for friendship at 2 and that the 4 asked him for friendship. Clear. Suppose you are asked for statistics in that sense, what your boss tells you: to see, I want a query to know, of the friends of 1 , how many did he ask for friendship and how many asked for it . With this design you have it completely solved.

Now, suppose you want to show user blocking situations 1 . You execute this query:

SELECT 
    ue.user_id id_emisor, 
    ur.user_id id_receptor,
    m.match_status
FROM matches_20180713 m
    LEFT JOIN users_20180713 ue ON ue.user_id=m.match_emisor
    LEFT JOIN users_20180713 ur ON m.match_receptor=ur.user_id
WHERE 
    (m.match_emisor=1 OR m.match_receptor=1) AND m.match_status>3;

In our column match_status I have previously agreed that the blocking states are from 4 , that's why the WHERE ... m.match_status>3 . The query will yield data like this:

id_emisor   id_receptor      match_status
    5             1               4
    1             7               6

We can read then that the user 5 asked the user friendship 1 and then block it (this is what the 4 represents in our numerical state convention). We also see that the user 1 asked the user% friendship 7 in a golden age but that relationship ended in a mutual block ... the golden years are over :(

This is my proposal for the solution of this so intricate problem. Maybe there are better solutions. It's a matter of trying and staying with the one that suits you best.

  

If you want to do a test on the data that I used to   reproduce the problem, you can see this DEMO EN   REXTESTER .

NOTE: I have given more descriptive names to the columns, so as not to get lost in the code and know the data I am dealing with. That's why the column names differ from yours. You can change that or leave it as you have it, at your choice. Anyway, it is convenient that the code is as understandable as possible, and using more descriptive names helps a lot.

I hope it serves you.

Add in WHERE the condition that relates the two users and matches tables:

SELECT u.*, m.* fROM users u, matches m
WHERE NOT EXISTS (
SELECT idblock
FROM blocked b
WHERE b.idBlock = u.id AND b.idBlocked = 9) AND u.id <> 9 
      AND u.id = m.id