MySQL Query matching multiple fields

Question

I have a database that has a table that has different LIKES that people have. Each like is stored as a different record. The only info I have in each record is userID, likeID.

The search will be based on the current userID that is signed in. So I'm not matching EVERYONE with EVERYONE but instead matching EVERYONE vs 1

So userID 45 might have likeID 3, likeID 6 and likeID 22 in the table (for instance)

What I want to do is have the table return, in descending order userIDs that they match with based on the total likes they match with someone else.

For example, (based on user 45 example above) it would look at userID 1 and see how many of likeID 3, likeID 6 and likeID 22 they have. then it'd go to userID 2 and see how many of likeID 3, likeID 6 and likeID 22 they have.....it'd do this for everyone.

Then it'd show the results for any that have more than 0 matches for those 3 likeIDs:

Matches:

You and UserID 12 share 3 of the same likes.

You and UserID 87 share 3 of the same likes.

You and UserID 22 share 2 of the same likes.

You and UserID 73 share 2 of the smae likes.

You and UserID 71 share 1 of the same likes.

etc...

I hope that explains what I'm trying to do. I don't think the mySQL query will be too hard but right now I'm baffled at how to do it!

THANKS IN ADVANCE!

DRapp · Accepted Answer · 2011-11-26 14:07:07Z

1

select
      UL2.userID,
      count(*) as LikeMatches
   from
      UserLikes UL1
         JOIN UserLikes UL2
            on UL1.LikeID = UL2.LikeID
           AND UL1.UserID != SingleUserBasisParameter
   where
      UL1.UserID = SingleUserBasisParameter
   group by
      UL2.UserID
   order by
      2 desc

The "2" in the order by is the ordinal column representing the COUNT(*)

answered Nov 26, 2011 at 14:07

DRapp

48.3k13 gold badges80 silver badges149 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

DRapp Over a year ago

@ypercube, Yup, but still good to see answers by other very talented minds like yourself too :)

user1066941 Over a year ago

THANK YOU THANK YOU THANK YOU!!! Just wanted to let you know the code was actually: AND UL2.userID != SingleUserBasisParameter where UL1.userID = SingleUserBasisParameter instead of AND UL1.UserID != SingleUserBasisParameter where UL1.UserID = SingleUserBasisParameter

Nicola Cossu · Accepted Answer · 2011-11-26 14:09:49Z

0

select userid,count(*) as common_likes
from table
where likeid in (select likeid from table where userid = 45) and userid <> 45
group by userid
order by common_likes desc

answered Nov 26, 2011 at 14:09

Nicola Cossu

56.7k15 gold badges96 silver badges99 bronze badges

Comments

Nick Zinger · Accepted Answer · 2011-11-26 14:12:05Z

0

how about:

SELECT COUNT(likes.likeID) as like_count FROM
LIKES as likes,
(SELECT UserID,likeID FROM likes WHERE UserID = $user_id) as user_likes
WHERE
user_likes.likeID = likes.likeID AND user_likes.userID != likes.userID
GROUP BY likes.UserID
ORDER BY like_count

I haven't tested it, but I think that it should at least give you the right idea. The 2nd SELECT finds all the likes of the main user, which you can then use to filter out the other likes.

answered Nov 26, 2011 at 14:12

Nick Zinger

1,1741 gold badge12 silver badges28 bronze badges

Collectives™ on Stack Overflow

MySQL Query matching multiple fields

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related