mySQL Query to find Users with complex specifications

  • Thread starter Thread starter DevPunk
  • Start date Start date
D

DevPunk

Guest
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?
 

Attachments

  • sdfsdfds_cr.webp
    sdfsdfds_cr.webp
    29.4 KB · Views: 19
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);
 
Unfortunately, I get this error message with all 3 queries.
Unknown column 'accepted_answer_id' in 'IN/ALL/ANY subquery'
 
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;
 
Back
Top Bottom