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
CodePudding user response:
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.