Translate IDs in single query
Posted: Tue Apr 13, 2010 1:28 pm
I have a SQL query like so:
I have a system_profiles table and inside each procedures tables there are 3 user ID's which must be translated, done in SQL. I cannot query once, then iterate that resultset and convert the ID's to user names using PHP -- MUST be done in a SQL query
I have it wrong but don't think I am far off. Maybe use a sub-select somehow to lookup the ID's
Code: Select all
SELECT
rp.id_primary AS `ID`,
rp.number AS `Rpi Number`,
rp.revision AS `Revision`,
CONCAT_WS(' ', sp.first_name, sp.last_name) AS `Originated by`,
CONCAT_WS(' ', sp.first_name, sp.last_name) AS `Approved by`,
CONCAT_WS(' ', sp.first_name, sp.last_name) AS `Revised By`
FROM
rpi_procedures AS rp,
system_profiles AS sp
WHERE
sp.id_user IN (rp.id_originated, rp.id_approved, rp.id_revised)
AND
(rp.id_status = 1 AND rp.id_task = 0)
I have it wrong but don't think I am far off. Maybe use a sub-select somehow to lookup the ID's