No takers??
> 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:3428
Subscription: http://www.houseoffusion.com/groups/sql/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/groups/sql/unsubscribe.cfm
