CODEWITHSUNDEEP
phpmysqlselectjoinleft-join
Evan Taylor
Evan Taylor

Reputation: 191

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 has 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.

Upvotes: 1

Views: 3740

Answers (1)

Darren
Darren

Reputation: 11011

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

Upvotes: 8

Related Questions