Simple MYSQL Query returns multiple rows

Question

I have the following table structure for my users and friends table.

users: first_name, last_name

friends: to_id, from_id, friend_status

So if users 4 and 7 are friends, I store them in the friend's table as 4 | 7 | 1 or 7 | 4 | 1 depending on who initiated the friendship. I do not do duplicate entries so 4 | 7 | 1 and 7 | 4 | 1 are not stored. Only one will be stored.

My problem is when I want to do a simple search for a friend whose name is Paul. Each of the following snippets below work fine by themselves but together, I get multiple rows that show inacurrate users who are not even friends.

SELECT u.* FROM `users` u, `friends` f WHERE CONCAT(u.first_name,' ',u.last_name) 
like '%paul%'  AND ((f.to_id=$logged_in_uid OR f.from_id=$logged_in_uid) 
AND f.friend_status=1);

Again, the above doesn't even return the friends, but the latter portion alone does bring back friends when used by itself.

Thanks and any help is truly appreciated :)

Jason Heo · Accepted Answer · 2013-12-18 03:38:04Z

1

there is no JOIN Condition between u and f.

could you try this?

SELECT u.*
FROM `users` u INNER JOIN `friends` f
    ON (u.user_id = f.to_id OR u.user_id = f.from_id) AND u.user_id != $logged_in_uid
WHERE CONCAT(u.first_name,' ',u.last_name) LIKE '%paul%'
    AND (
      (f.to_id=$logged_in_uid OR f.from_id=$logged_in_uid) 
      AND f.friend_status=1
    );

Or use UNION to get rid of OR which make things complicated and can't use index.

SELECT u.*
FROM `users` u INNER JOIN `friends` f ON u.user_id = f.from_id
WHERE CONCAT(u.first_name,' ',u.last_name) LIKE '%paul%'
    AND f.to_id=$logged_in_uid AND f.friend_status=1

UNION

SELECT u.*
FROM `users` u INNER JOIN `friends` f ON u.user_id = f.to_id
WHERE CONCAT(u.first_name,' ',u.last_name) LIKE '%paul%'
    AND f.from_id=$logged_in_uid AND f.friend_status=1

edited Dec 18, 2013 at 3:38

answered Dec 18, 2013 at 3:32

Jason Heo

10.3k2 gold badges39 silver badges72 bronze badges

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

Collectives™ on Stack Overflow

Simple MYSQL Query returns multiple rows

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related