Page 1 of 1

Translate IDs in single query

Posted: Tue Apr 13, 2010 1:28 pm
by alex.barylski
I have a SQL query like so:

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

Re: Translate IDs in single query

Posted: Tue Apr 13, 2010 2:25 pm
by alex.barylski
Nevermind I figured it out!!! :D

It's the most complicated (at least verbose) SQL I have ever written :)