Hey Rich, I'm not sure I understand what you are trying to get. Is the info
that you are trying to get any different than the Users.First and Users.Last as
the first two fields in the SELECT clause?
Jon
________________________________________
From: Rich [ri...@twcny.rr.com]
Sent: Wednesday, November 23, 2011 12:32 AM
To: sql
Subject: Re: Query Based on Query Result
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:3429
Subscription: http://www.houseoffusion.com/groups/sql/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/groups/sql/unsubscribe.cfm
