I have a working query below but I need to select first and last names for these agents in my select statement, without any real value to join the table on.

My other table is ambition.ambition\_users and it has first\_name, last\_name and extension. I have the subqueries pulling the extensions correctly and treating it as `id`. However, in my main select, I want to also select first\_name and last\_name. I can't seem to find a way to do this with a simple subquery and I don't have anything to join the table on UNLESS I'm able to join my created `id` on ambition\_users.extention. 

What's the best way to achieve pulling first and last name without affecting the aggregations of the current query?

Here's the working query:

```
select
case 
when callingpartyno       in (select extension from ambition.ambition_users)
then callingpartyno
when finallycalledpartyno in (select extension from ambition.ambition_users)
then finallycalledpartyno
 end as id

-- this is where i want to select first_name and last_name from ambition.ambition_users

, sum(duration) as total_talk_time_seconds
, round(sum(duration) / 60,2) as total_talk_time_minutes
, sum(if(legtype1 = 1,1,0)) as total_outbound
, sum(if(legtype1 = 2,1,0) and answered = 1) as total_inbound
, sum(if(legtype1 = 2,1,0) and answered = 0) as total_missed
, sum(if(legtype1 = 1, 1, 0)) +                   -- outbound calls
 sum(if(legtype1 = 2, 1, 0))  as total_calls
, now() as time_of_report
, curdate() as date_of_report
 from 
  ambition.session a
  join ambition.callsummary b
  on a.notablecallid = b.notablecallid
 where 
date(a.ts) >= curdate()
 and (
callingpartyno in (select extension from ambition.ambition_users
)
 or  finallycalledpartyno in (select extension from ambition.ambition_users
)
)
 group by
 id;

```