many fields linked to one

[Jassim Rahma]

Hi, i have the table lab_inestigations which has created_by, processed_by and closed_by and all three columns are integer holding a user id (systm_user_id) from a table sysem_users but of course each created_by, processed_by and closed_by will have different systm_user_id value.. How can I make such sql statement to rertive the system_user_name for all three columns from system_users? Thanks, Jassim

[Mycroft Holmes]

You need to make 3 joins to the user table, one for each of your user fields in lab_inestigations, something like

SELECT *
FROM lab_inestigations I
INNER JOIN sysem_users S1 ON SI.UserID = I.CreatedBy
INNER JOIN sysem_users S2 ON S2.UserID = I.ProcessedBy
INNER JOIN sysem_users S3 ON S3.UserID = I.ClosedBy

Never underestimate the power of human stupidity RAH