How to correctly JOIN 3 tables

Question

I am trying to create a database that contains users (general) and two more tables, that is: male / female choice. This will be used for dating, more specifically the fact that a girl votes for a boy, a boy for a girl. If their choices are overlapping, it means that they fit together.

I do not know if I approached the topic well, but so far I have built the following code:

USERS:
| ID (PRIMARY) |  NAME  | GENDER |
|       1      | Man1   | Male   |
|       2      | Man2   | Male   |
|       3      | Woman1 | Female |
|       4      | Woman2 | Female |

Woman_Result
| ID (PRIMARY) | ID_PERSON | ID_CHOOSE |
|       1      |    3      |    1      |
|       2      |    3      |    2      |
|       3      |    4      |    1      |

Man_Result
| ID (PRIMARY) | ID_PERSON | ID_CHOOSE |
|       1      |    1      |    4      |
|       2      |    2      |    1      |

So the match of those people is only: Man1 and Woman2

SELECT  w.*, m.*
    FROM  Man_Result AS m
    JOIN  Woman_Result AS w
    WHERE w.ID_CHOOSE = m.ID_PERSON AND m.ID_CHOOSE = w.ID_PERSON

OUTPUT:

| ID | ID_PERSON | ID_CHOOSE | ID | ID_PERSON | ID_CHOOSE |
| 1  |     1     |     4     | 1  |     4     |     1     |

So its working, but how i can connect it to my USERS table to get those output:

NAME | NAME
Man1 | Woman2

I have combined with something like this, but I don't know completely how to combine it to make it work :)

SELECT USERS.NAME
FROM USERS
INNER JOIN Woman_Result ON USERS.ID = Woman_Result.ID_PERSON
INNER JOIN Man_Result ON USERS.ID = Man_Result.ID_PERSON

-- EDIT --

If I wanted to add "ID_CHOOSE2", "ID_CHOOSE3" etc to each user, what kind of query would I have to create for it to be correct? I Tried use "OR":

ON w.ID_CHOOSE OR w.ID_CHOOSE2 = m.ID_PERSON AND m.ID_CHOOSE OR m.ID_CHOOSE2 = w.ID_PERSON

And

ON (w.ID_CHOOSE = m.ID_PERSON AND m.ID_CHOOSE = w.ID_PERSON)
OR (w.ID_CHOOSE2 = m.ID_PERSON AND m.ID_CHOOSE2 = w.ID_PERSON)

But the second example only compares the same columns, and my point is that it compares each to each as in the example with one person

My code: https://onecompiler.com/mysql/3xz4552uv

Answer 1

Ok, so this is the query that you should be using to get your results back if you decided to this in ONE TABLE called matches

id  uid1  uid2
1   1     4
2   2     1
3   3     1
4   3     2
5   4     1
6   1     2

The matches table has id, uid1, uid2, and a primary key which is the combination of (uid1,uid2)

Then your query would be something like this

SELECT
    C.name, D.name
FROM matches A
INNER JOIN matches B 
    ON A.uId1 = B.uId2 AND A.uId2 = B.pId1 AND B.Id > A.Id
INNER JOIN users C ON A.uId1 = C.Id
INNER JOIN users D ON A.uId2 = D.Id

Or you can do it with CTE like the following which is a bit faster:

;WITH DATA AS (
SELECT
    A.*
FROM matches A
INNER JOIN matches B 
    ON A.uId1 = B.uId2 AND A.uId2 = B.uId1 AND B.Id > A.Id )

SELECT
    C.name, D.name
FROM DATA A
INNER JOIN users C ON A.uId1 = C.Id
INNER JOIN users D ON A.uId2 = D.Id

If you don't have the ability to change your SQL table and how it is structured I would do it using a temp variable like the following:

DECLARE @STARTDATA TABLE ( uid1 int, uid2 int, primary key (uid1, uid2) )

INSERT INTO @STARTDATA (uid1, uid2)
SELECT
    A.uid1, A.uid2
FROM matches A
INNER JOIN matches B 
    ON A.uId1 = B.uId2 AND A.uId2 = B.uId1 AND B.Id > A.Id

SELECT
    C.name, D.name
FROM @STARTDATA A
INNER JOIN users C ON A.uId1 = C.Id
INNER JOIN users D ON A.uId2 = D.Id

Hope this helps!

Answer 2

You must join 2 copies of USERS to your current join.
The 1st copy will return the man's name and the 2nd will return the woman's name:

SELECT um.NAME AS man_name,
       uw.NAME AS woman_name
FROM Man_Result AS m JOIN Woman_Result AS w 
ON w.ID_CHOOSE = m.ID_PERSON AND m.ID_CHOOSE = w.ID_PERSON
JOIN USERS AS um ON m.ID_PERSON = um.ID
JOIN USERS AS uw ON w.ID_PERSON = uw.ID;

See the demo.