Hi,
I have three tables:
Users: staff data
Sessions: log of staff accessing client records
Clients: client data
I'm doing a search where I get the following data for sessions where the staff
in question (users 1, 2, and 3) have accessed client records:
SELECT
Users.First,
Users.Last,
Sessions.UserID,
Sessions.ClientID,
Clients.FirstName,
Clients.LastName
Clients.StaffID,
FROM Users, Sessions, Clients
WHERE Sessions.UserID IN ('UserID_1','UserID_2','UserID_3')
AND Users.ID=Sessions.UserID
AND Sessions.ClientID=Clients.ClientID;
So this gives me the data I need as long as those users exist and the client
IDs in the Sessions and Clients tables match.
However, I also want to get the first and last name of the person corresponding
to the StaffID variable. The StaffID variable in the Clients table is the same
as the ID variable in the Users table. Any suggestions?
I don't want to change the resulting data set from the query above by adding to
the WHERE condition. I just want the first and last name of the staff member
whose StaffID I get in the results. It's as if I were to add something to the
items in the SELECT statement above so it would look like:
SELECT
Users.First,
.
.
.
Clients.StaffID,
(SELECT Users.First, Users.Last FROM Users WHERE Users.ID=Clients.StaffID)
.
.
.
FROM
...and so on...
So I get Clients.StaffID normally, but the next line is what I need, again
acknowledging that this is not the correct syntax/method. Just trying to
convey what I need here.
Thanks!
Rich
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Order the Adobe Coldfusion Anthology now!
http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion
Archive: http://www.houseoffusion.com/groups/sql/message.cfm/messageid:3427
Subscription: http://www.houseoffusion.com/groups/sql/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/groups/sql/unsubscribe.cfm
