Displaying Foreign Key data

[Silentking]

I have a table in a database that refers to both string data and foreign key
IDs. When I request information from the table, how do I display the text
instead of the foreign key using Coldfusion?

Example
Work_Order Table
workorderID - 1
workorderName - need help
staffFirstName - 1
staffLastName - 2

Staff_Name Table
staffnameID - 1
staffFirstName - John
staffLastName - Doe

[PaulH]

depends somewhat on the db you're using but maybe something like this:

SELECT w.workorderID,w.workorderName,s.staffFirstName,s.s taffLastName
FROM Work_Order w
INNER JOIN Staff_Name s ON w.staffNameID = s.staffNameID
ORDER BY w.workorderID

---or---

SELECT w.workorderID,w.workorderName,s.staffFirstName,s.s taffLastName
FROM Work_Order w, Staff_Name s
WHERE w.staffNameID = s.staffNameID
ORDER BY w.workorderID

[Silentking]

The database that I am using is MySQL ver 4.0.18

[Silentking]

Maybe we are confused at what I am trying to accomplish so let me start over.

I have 3 tables at the moment. work_order, staff_name, site_name.

In my work_order table are the following fields.

workOrderID
workOrderName
siteNameID - from site table
staffNameID - from staff table
hardwareType
softwareType
etc

What is important is the siteNameID and staffNameID from the other tables.
When I do a search for a work order at the moment I do a Select * FROM
work_order; to get information from the work_order table. The information looks
similar to this

workorderID: 9
workOrderName: PC is broken
siteNameID: 9
staffNameID: 5
hardwareType: PC
softwareType: n/a

For the staffNameID and siteNameID parts of the query instead of displaying
the exact number that references the siteNameID or staffNameID, I would like to
replace it with the corresponding siteName from the site_name table, and the
corresponding staffFirstName and staffLastName from the staff_name table. So
when I perform a query on from the my work_order application it displays the
information like this:

workorderID: 9
workOrderName: PC is broken
siteNameID: Some Site
staffNameID: John Doe
hardwareType: PC
softwareType: n/a

I want the query to say look in the site_name table and find siteNameID that
is equal to 9, and give me the siteName out of Some Site instead of 9 which is
does now.

I hope this doesn't confuse you.

[Silentking]

But believe it or not I may have stumbled upon a temporary solution.