How to correctly JOIN 3 tables

April 8, 2022

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

>Solution :

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.