Yes, it's different. It's Users.First and Users.Last, but NOT based on "WHERE
Sessions.UserID IN ('UserID_1','UserID_2','UserID_3')"
It comes from the same Users table, but it's the user based on the StaffID that
comes from the query results.
Rich
On Nov 23, 2011, at 6:50 AM, Hallenberg, Jon wrote:
>
> 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:3432
Subscription: http://www.houseoffusion.com/groups/sql/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/groups/sql/unsubscribe.cfm
