SQL For Cross Table Query With Multiple References to Same Column.

Hi,

I have a table that contains information in individual jobs, called "case". The table stores various staff IDs which relate to various tasks carried out by members of staff. The staff details stored in a seperate table called "Staff". I'm trying to create a query that returns the job details with the surnames of the staff instead of their IDs in order to make it clearer for the user.

For Example.
A particular job can have a person who receives it, a person who deals with it and a person who finalises it. All of these roles can, and are likely to be done by different people. A persons ID is simply a number which is stored in my Case table in the column relating to the task. I want my query to return the surname that relates to the stored ID.

The problem I see is that is I use a traditional query on multiple tables I cannot specify which surname should ge where. Normally I would use something like this;
"SELECT c.ID, s.surname
FROM Case c, Staff s
WHERE s.ID = c.receivedBy"

However I need to return differing surnames (s.surname) depending on the task;

If I used something like this to try and return the id of each job along with the surnames of the person receiving and the person finalising, the I would only get one surname returned and how would the DB know which surname it should return.
"SELECT c.ID, s.surname
FROM Case c, Staff s
WHERE s.ID = c.receivedBy
AND s.ID = c.finalised"

I even thought of doing something like this to assign each surname an individual reference, but how can I match my references to a value in a particular column.
EG.
"SELECT c.ID, s.Surname received, s.surname dealing, s.surname finalise
From Case c, Staff s
WHERE c.recieved = s.id
AND c.dealing = s.id
AND c.finalise = s.id"

Does this make sense to anyone?? I'm finding it quite hard to explain let alone solve.

Thanks
JasonStart Free Trial