Two left joins and a union in MySQL

Question

I'm trying to do a pretty complex query in MySQL; complex for me, at least.

Here's an example of what I'm trying to do:

SELECT * FROM friends
LEFT JOIN users ON users.uid = friends.fid1
LEFT JOIN users ON users.uid = friends.fid2
WHERE (friends.fid1 = 1) AND (friends.fid2 > 1)
UNION SELECT fid2 FROM friends
WHERE (friends.fid2  = 1) AND (friends.fid1 < 1)
ORDER BY RAND()
LIMIT 6;

---

I'm getting back: ERROR 1066 (42000): Not unique table/alias: 'users'.

Where am I going wrong, and how should I really be performing this query?

Answer 1

Alias your table, like:

LEFT JOIN users u1 ON u1.uid = friends.fid1
LEFT JOIN users u2 ON u2.uid = friends.fid2