开发者

Join Two Columns With One Table MySQL

I am creating a php todo list and have an 'admin_todo' table in a MySQL DB and each todo item has a 'created_by' and 'completed_by' column which contains the id of a user in in a seperate table 'admin_users'.

I need a query that will allow me to link the 'created_by' and 'completed_by' columns with the 'username' column in the 'admin_users' table such that I can echo out the username of a user who h开发者_开发问答as created the todo and who has completed the todo. It seems like it would be ean easy thing to do but I cannot find a query to do this properly.

I have tried using a union:

SELECT admin_todo.*, admin_users.username AS creator 
FROM admin_todo 
LEFT JOIN admin_users ON admin_todo.created_by = admin_users.id 

UNION ALL

SELECT admin_todo.*, admin_users.username AS completer 
FROM admin_todo 
LEFT JOIN admin_users ON admin_todo.completed_by = admin_users.id

But alas, it returns all todo entries twice, the first time with the usernames of the todo creators under the 'creator' column. The second time with the usernames of the todo completers but ALSO under the 'creator' column.


Try this:

SELECT admin_todo.*, au.username AS creator, au2.username AS completer
FROM admin_todo 
LEFT JOIN admin_users AS au
    ON admin_todo.created_by = au.id
LEFT JOIN admin_users AS au2
    ON admin_todo.completed_by = au2.id
0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜