SELECT
Users.First,
Clients.StaffID,
(SELECT Users.First + ' ' + Users.Last
FROM Users
WHERE Users.ID=Clients.StaffID) as username
FROM
..and so on...
Kristen A. Winsor, PMP
Project Manager, Information Technology
-----Original Message-----
From: Rich [mailto:ri...@twcny.rr.com]
Sent: Wednesday, November 23, 2011 12:33 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:3430
Subscription: http://www.houseoffusion.com/groups/sql/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/groups/sql/unsubscribe.cfm
