Selecting from another table without a join

Question

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;

Well I'm selecting from the main table and joining another because they contain the call info that aggregates the numbers, and that's all working properly. However, I just want to select the names for the agents based on their extensions — Geoff_S
Knowing your previous questions I would rewrite your CASE expression and use it for a join. — Paul Spiegel

Valli Valli · Accepted Answer · 2017-10-30T18:19:27

Why you need so many subqueries. You can use a left join and take out all other subqueries.

    select
    COALESCE( callingpartyno, finallycalledpartyno) as id
    -- if extension is not equal to callingpartyno, it will return null because its a left join. If you use coalesce you can get the first non null value.

    , max(c.firstname)
    , max(c.lastname)
    -- if you use max you need not put that in group by clause

    , 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
---- adding a left join
    left join 
     ambition.users c
    on c.extension = callingpartyno or c.extension = finallycalledpartyno
     where 
    date(a.ts) >= curdate()
     group by id;

Selecting from another table without a join

3 Answers