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