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

Ray · Accepted Answer · 2022-04-08 20:32:49Z

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!

Thanks. :) I edited my topic and have next one question (choose2). Do You have any idea?

forpas · Accepted Answer · 2022-04-08 17:25:22Z

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.

Hello, i edited my post and add choose2 to check. Can You Help me?
@Karmay if you have a new requirement you should ask a new question and not change the current question because this way you invalidate the answers of the original question.

Collectives™ on Stack Overflow

How to correctly JOIN 3 tables

2 Answers 2

1 Comment

2 Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

2 Comments

Related