CODEWITHSUNDEEP
phpmysql
JosCarrillo
JosCarrillo

Reputation: 85

How to replace two user id columns with names?

I have a table with two columns.

created_by | assigned_to

One is the person who create a "to do card", the other column is the person who is assigned to do it.

The columns are filled with id, for example; (1,2,3).

My question is how can i replace this id with the names that are saved in another table.

by far this is what i got.

SELECT tickets.*, users.Name, users.LastName
FROM tickets
LEFT JOIN users ON tickets.assigned_to = users.uid
AND tickets.id_usuario = users.uid

but when i try to print the name of the persona

Created by: <?= query['Name']; ?>

Assigned to: <?= query['Name']; ?>

but i got the same name in both columns.

Upvotes: 3

Views: 373

Answers (1)

Phil
Phil

Reputation: 164897

Your current query only joins to users when the creator and assigned-to user ID are the same.

You'll need to join to your users table twice to retrieve the separate records. You do this by adding more joins...

SELECT
  c_users.Name as created_by_name,
  c_users.LastName as created_by_last_name,
  a_users.Name as assigned_to_name,
  a_users.LastName as assigned_to_last_name
FROM tickets t
INNER JOIN users c_users
  ON t.created_by = c_users.uid
  -- or t.id_usuario = c_users.uid, it is not clear from your question
INNER JOIN users a_users
  ON t.assigned_to = a_users.uid

I've used INNER JOIN instead of LEFT JOIN as it doesn't seem feasible that you'd have broken relationships between the two tables.

Upvotes: 1

Related Questions