mySQL Query to find Users with complex specifications

[DevPunk]

Of course, this request has nothing to do with XF.

I am looking for a query with which I can find users with whom I have in common - and - who have in common with me.

These successes are based on the scheme:
User X gives an answer (user_answer_id), but only accepts users whose user_answer_id matches those allowed by user X to answer (accepted_answer_id).

Only users with which I have in common are output. So far so good.

Now comes another factor. Now I know that the users match me. At the same time user X has to be compared to the users found before.

Conclusion:
I would like to receive only the users as result, if there were at least 2 mutual similarities. It does not have to be the same (question_id) main thing is that 2 mutually accepted answers match...


In the attachment an excerpt from the table

Example:
I am user 2.

User 1 would not fit into the selection, because he has only one thing in common with me.

user 3 would fit from my point of view... countercheck from his point of view... it fits also in each case more than 2 similarities.

user 4 falls out...
From user 2's point of view he has 2 similarities with 4, but 4 has only 1 with user 2...


Who can help me with this?

 

[Vekseid]

select user_id from {{table}} where user_id != {{userid}} and user_answer_id in (select accepted_answer_id from {{table}} where user_id = {{userid}}) having count(user_answer_id) >= 2;

select user_id from {{table}} where user_id in (select user_id from {{table}} where user_id != {{userid}} and accepted_answer_id in (select user_answer_id from {{table}} where user_id = {{userid}}) having count(accepted_answer_id) >= 2);

To merge these together

select user_id from {{table}} where user_id in (select user_id from {{table}} where user_id != {{userid}} and accepted_answer_id in (select user_answer_id from {{table}} where user_id = {{userid}}) having count(accepted_answer_id) >= 2) and user_id in (select user_id from {{table}} where user_id != {{userid}} and user_answer_id in (select accepted_answer_id from {{table}} where user_id = {{userid}}) having count(user_answer_id) >= 2);

 

[DevPunk]

Unfortunately, I get this error message with all 3 queries.
Unknown column 'accepted_answer_id' in 'IN/ALL/ANY subquery'

 

[Vekseid]

Try adding aliases, sorry.

Does this work?

select o.user_id from {{table}} as o where o.user_id != {{userid}} and o.user_answer_id in (select p.accepted_answer_id from {{table}} as p where p.user_id = {{userid}}) having count(p.user_answer_id) >= 2;