CODEWITHSUNDEEP
phpmysqlsql
Sebastian Farham
Sebastian Farham

Reputation: 825

How I can find matching data row in Mysql table?

Been beating my head around trying to find a single query that will find matching rows.

I have this table called likes:

id|UserId|LikeUserId|Date|

Query to retrieve everyone who likes me:

SELECT * FROM ve_wholikes l 
JOIN ve_users u ON l.UserId=u.UserId 
WHERE l.LikeUserID = '$loginid_session'

Query to retrieve everyone I like:

SELECT * FROM ve_wholikes l 
JOIN ve_users u ON l.LikeUserId=u.UserId 
WHERE l.UserID = '$loginid_session'

What would be the query to find out every row that match (I like them & they like me)?

Upvotes: 1

Views: 170

Answers (1)

Don't Panic
Don't Panic

Reputation: 41810

You can join the ve_wholikes table to itself on UserId = LikeUserId and vice versa to find mutual likes.

SELECT l1.*, u.* FROM 
ve_wholikes l1
INNER JOIN ve_wholikes l2 ON l1.UserId = l2.LikeUserId AND l1.LikeUserId = l2.UserId
INNER JOIN ve_users u ON l1.LikeUserId = u.UserId
WHERE l1.UserId = ?;

The records from the ve_users table will be the users that liked you. I just selected one of the ve_wholikes tables. You could select columns from l2 as well if you need to see the date when they liked you back.

Upvotes: 2

Related Questions