Query Based on Query Result

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

No takers?? ----- Excess quoted text cut - see Original Post for more -----

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 [rich2@twcny.rr.com] Sent: Wednesday, November 23, 2011 12:32 AM To: sql Subject: Re: Query Based on Query Result No takers?? ----- Excess quoted text cut - see Original Post for more -----

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: ----- Excess quoted text cut - see Original Post for more -----

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 No takers?? ----- Excess quoted text cut - see Original Post for more ----- the staff in question (users 1, 2, and 3) have accessed client records: ----- Excess quoted text cut - see Original Post for more ----- 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: ----- Excess quoted text cut - see Original Post for more ----- again acknowledging that this is not the correct syntax/method.  Just trying to convey what I need here. > > Thanks! > > Rich

Thanks, Kristen!  So I just needed that "as username" part!  I was so close! For whatever reason, the "+" symbol was not working, at least with the version of MySQL I'm using: > (SELECT Users.First + ' ' + Users.Last FROM Users WHERE Users.ID=Clients.StaffID) as username I found that I needed to use one of the two options below: (SELECT Users.First FROM Users WHERE Users.ID= Clients.StaffID) as StaffFirst, (SELECT Users.Last FROM Users WHERE Users.ID= Clients.StaffID) as StaffLast, ...or this... (SELECT CONCAT(Users.First,' ',Users.Last) FROM Users WHERE Users.ID= Clients.StaffID) as StaffName, Thanks again! Rich On Nov 23, 2011, at 8:28 AM, Kristen Winsor wrote: ----- Excess quoted text cut - see Original Post for more -----