I am assuming that you have ASSIGNEDTO set to the same as USERID? In that case, this ANSI SQL statemet should work:

SELECT *,
(SELECT FIRSTNAME + ' ' + LASTNAME FROM USERS WHERE USERID = TICKETS.ASSIGNEDTO)AS ASSIGNEDNAME,
(SELECT FIRSTNAME + ' ' + LASTNAME FROM USERS WHERE USERID = TICKETS.OPENEDBY) AS OPENEDNAME,
(SELECT FIRSTNAME + ' ' + LASTNAME FROM USERS WHERE USERID = TICKETS.REQUESTEDBY) AS REUQESTEDNAME
FROM TICKETS
WHERE ASSIGNEDTO = FOO

Welcome to the wonderful world of sub-queries. You didn't say which database, so I am assuming ANSI SQL. Your millage may vary, please let me know if it works, I didn't have a chance to test it. It may need some tweaking. I think the parenthesis are needed. But I haven't done SQL since November, and my brain has lost some of my knowledge or I've forgotten it.