I have a table named "code" that I use for miscellaneous values in a CMS. For example, on a form where there is a picture, the table would store the values "left", "center" and "right" for the alignment of an image or text. The table has 3 columns - code_type, code_value and code_desc. So for this example, one row would be "align", 1, "left". To get the alignment, I woudl do a join of the content and code tables.
I have an example where I would like to join multiple values in a content table - a staff table - with the code table. The code_types would be "prefix" and "suffix" (for Dr. and PhD, respectively) in addition to the one I have for the content category. The code I have working with the category join is as follows:
(Note - the Val() is a ColdFusion command)
SELECT code_description as category_desc, prefix, first_name, middle_init, last_name, suffix, title, phone, email
FROM staff, code
WHERE (code_type = 'STAFFCATEGORY'
AND category = val(code_value))
ORDER BY category, priority
Any assistance with how I can enhance this query to get the description values for the prefix and suffix would be greatly appreciated.