What kind of query i need to compare this values-CodePudding

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)

As you can see, the query is not working well because it is poorly structured.

I mean a comparison of the type: A person chose some person (choose) and another (choose2). Check if the other person chose the first person in all their choices (choose, choose2 etc). If so, they match, if not, do not display and check others people. I want output like:

TOM W  | KATE B
MARK K | KATE B
MARK K | ALEX S

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

-- EDIT FOR MORE INFO --- I have a table with men and women (users) And two other tables containing the choices of men and women

Now I would like to find a match (who matches whom based on choices in "Choose" columns - they are not always in sequence. Sometimes it's 3,2,1 and sometimes it's 1,3,2) This is illustrated in the example below:

ID_PERSON MAN:

1 - JACK  - Choose: 1,3,2 (Mary, Emily, Lilly)
2 - TOM   - Choose: 1,2,3 (Mary, Lilly, Emily)
3 - HARRY - Choose: 2,1   (Lilly, Mary)

| ID | ID_PERSON | ID_CHOOSE | ID_CHOOSE2 | ID_CHOOSE3  |
| 1  |      1    |     1     |     3      |     2       | 
| ID | ID_PERSON | ID_CHOOSE | ID_CHOOSE2 | ID_CHOOSE3  |
| 2  |      2    |     1     |     2      |     3       | 
| ID | ID_PERSON | ID_CHOOSE | ID_CHOOSE2 | ID_CHOOSE3  |
| 3  |      3    |     2     |     1      | NULL(empty) |

ID_PERSON WOMAN:

1 - Mary  - Choose: 3,1,2 (Harry, Tom, Jack)
2 - Lilly - Choose: 1,2   (Jack, Harry)
3 - Emily - Choose: 1,2,3 (Jack, Tom, Harry)

| ID | ID_PERSON | ID_CHOOSE | ID_CHOOSE2  | ID_CHOOSE3 |
| 1  |      1    |     3     |     1       |     2      | 
| ID | ID_PERSON | ID_CHOOSE | ID_CHOOSE2  | ID_CHOOSE3 |
| 2  |      2    |     1     | NULL(empty) |     2      | 
| ID | ID_PERSON | ID_CHOOSE | ID_CHOOSE2  | ID_CHOOSE3 |
| 3  |      3    |     1     |     2       |     3      |

Match:
JACK  Matched with: Mary, Emily, Lilly
TOM   Matched with: Mary, Emily
Harry Matched with: Lilly, Harry

Thanks

CodePudding user response：

Here is an alternative schéma.
See dbFiddle link below.

SELECT * FROM users;
SELECT * FROM choices;

 id | name     | gender
--: | :------- | :-----
101 | KATE B   | F     
102 | ALEX S   | F     
203 | TOM W    | M     
204 | MARK K   | M     
205 | DENNIS A | M     

chid | chooser | choice
---: | ------: | -----:
   1 |     203 |    101
   2 |     203 |    102
   3 |     204 |    102
   4 |     204 |    101
   5 |     101 |    203
   6 |     101 |    204
   7 |     102 |    204
   8 |     102 |    205

select 
m.id, m.name man,
f.id, f.name woman
from
choices c 
join choices s 
on c.chooser = s.choice
left join users m on m.id = c.chooser
left join users f on f.id = c.choice
where s.chooser = c.choice
and m.gender = 'M'
and f.gender = 'F';

 id | man    |  id | woman 
--: | :----- | --: | :-----
203 | TOM W  | 101 | KATE B
204 | MARK K | 101 | KATE B
204 | MARK K | 102 | ALEX S

db<>fiddle here