Geek Answers Handbook

Two left joins and join in MySQL

I am trying to make a rather complicated query in MySQL ; complex for me at least.

Here is 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 am returning: ERROR 1066 (42000): Not unique table/alias: 'users' .

Where am I mistaken and how should I fulfill this request?

+4
source share
2 answers

Your table alias, for example:

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

You recorded a left join twice with different table fields, it seems like when he got the parsing, give them an alias and then attach them to friends Table

 LEFT JOIN users users1 ON users1.uid = friends.fid1 LEFT JOIN users users2 ON users2.uid = friends.fid2
+2
source

More articles:


All Articles