Translate IDs in single query

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
alex.barylski
DevNet Evangelist
Posts: 6267
Joined: Tue Dec 21, 2004 5:00 pm
Location: Winnipeg

Translate IDs in single query

Post 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
alex.barylski
DevNet Evangelist
Posts: 6267
Joined: Tue Dec 21, 2004 5:00 pm
Location: Winnipeg

Re: Translate IDs in single query

Post by alex.barylski »

Nevermind I figured it out!!! :D

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