JOINS and Splitting Info from Second Table into Two Columns

[WebMachine]

I want to build a mySQL query for the following situation: the ‘clients’ table has fields id, first_name, last_name, coordinator_id, careplanner_id. Because the coordinators and care planners are basically the same except for their roles, they are found in the ‘users’ table and I want to join this so that the coordinator and care planner columns display their names, not their IDs.

If I only need the coordinator, it works like this:

SELECT clients.first_name AS 'First Name', clients.last_name AS 'Last Name', CONCAT(users.first_name, ' ' , users.last_name) AS 'Coordinator'
FROM clients 
INNER JOIN users ON users.id = clients.coordinator_id
ORDER BY clients.last_name ASC; 

but how then do I also include the same thing for the care planner in this query?

If I also use CONCAT(users.first_name, ' ' , users.last_name) AS 'Care Planner' and INNER JOIN users ON users.id = clients.careplanner_id I get a conflict.

I tried nesting queries (new to me) with no luck.

This is all with the purpose of creating a table to be exported to an .xlsx spreadsheet and I want the following columns: First Name, Last Name, Coordinator, Care Planner.

[fretburner]

You need to join the users table twice, once for each record. You also need to alias the tables so that the DB engine knows which table you’re referring to in your SELECT clause.

SELECT clients.first_name AS 'First Name', clients.last_name AS 'Last Name', 
	CONCAT(u1.first_name, ' ' , u1.last_name) AS 'Coordinator',
	CONCAT(u2.first_name, ' ' , u2.last_name) AS 'Care_planner'
FROM clients 
INNER JOIN users u1 ON u1.id = clients.coordinator_id
INNER JOIN users u2 ON u2.id = clients.careplanner_id
ORDER BY clients.last_name ASC;

[WebMachine]

That’s what I was missing - giving the users table different aliases. Thank you. I will try that right now. Okay, that worked - problem solved.