Left Join query return bad results

Question

I've been trying for hours to make a left join query with MySQL.
I've a table called "at_friends" where I store the relationships between users.

id  |  id_user1 |  id_user2  |  accepted
1   |  2        |  1         |  1
2   |  1        |  3         |  0

It means that user 1 is friend with user 2, and user 1 sent a friend request to user 3.

And here is my table "at_users"

id  |  name
1   |  "John"   
2   |  "Mike"   
3   |  "Bob"    

I've tried this query :

SELECT at_users.id, at_users.name
    FROM at_users 
    LEFT JOIN at_friends
         ON at_friends.id_user1 = at_users.id
         OR at_friends.id_user2 = at_users.id
         AND at_friends.accepted = 1
    WHERE id_user1 = 1 OR id_user2 = 1

"1" is the unique id of the current user (John)

But I get these results

id  |  name
1   |  "John"   
2   |  "Mike"   
1   |  "John"    

What I'm trying to have is the list of the accepted friends of the user n°1.
By the way, I use Laravel, but I don't know if there is a way to do this without a custom mysql query.

Answer 1

Try this:

SELECT u.id, u.name
FROM at_users u 
INNER JOIN (SELECT id_user2 AS friendID FROM at_friends WHERE id_user1 = 1 AND accepted = 1
            UNION 
            SELECT id_user1 AS friendID FROM at_friends WHERE id_user2 = 1 AND accepted = 1
           ) AS A ON u.id = A.friendID

Answer 2

An alternative that only requires a single scan of the friends table:

SELECT u.id, u.name
FROM at_users u 
JOIN at_friends f
  ON 1 in (f.id_user1, f.id_user2) and
     u.id = case f.id_user1 when 1 then id_user2 else id_user1 end and 
     f.accepted=1

SQLFiddle here.