giant query question

[jreider]

ok, I got tons of useful help on this board with my last problem. Much thanks to those who led me in the right direction

Here's my new issue:

I have a query that returns all total hours worked by our techs in a certain day, if that total falls below the minimum hours needed, an e-mail gets generated to the tech. I have a query below that returns everything I need, but I want to know if there is a way for me to loop through each user, regardless if they have any hours for the day or not, and pass their user ID into this query. I can do it with PHP, but I don't want to run the query X amount of times to get each result. The query is below, it works and returns everything I need, but only if a technician has time entered for the day. Is there a way to encapsulate this query inside of another that simply selects the technician ID and passes it into the query below?

select hours,userN,daily,tEmail
from(
select (totalBillable - daily) as hours
             ,totalBillable
             ,userN
            ,daily
            ,tEmail
from(
select IFNULL(sumtot,0)+IFNULL(sumgen,0) + IFNULL(sumgenNB,0)+IFNULL(sumtotNB,0) as totalBillable,
             userN,
             daily,
             tEmail
 from(
select round(sum(tot),2) as sumtot
     , round(sum(gen),2) as sumgen
     ,round(sum(genNB),2) as sumgenNB
     ,round(sum(totNB),2) as sumtotNB
     , userN
     ,daily
     ,tEmail
      from (
        select sum(tech_notes.time_spent)          as tot 
              , null                                as gen
              , null 			 as totNB
              , null			 as genNB
              , tech_notes.tech_notes_ticket_tech   as userN
              ,users.dailyHours as daily
              ,users.tech_email as tEmail
           from tech_notes
         
           join new_tickets 
             on tech_notes.ticket_unique_id
              = new_tickets.ticket_unique_id
           join users
             on users.tech_user = tech_notes.tech_notes_ticket_tech
           where new_tickets.billable IS NULL
            and new_tickets.ticket_status <> "new"
            and tech_notes.tech_notes_real_date = '2004-12-01'
                    
         group 
             by tech_notes.tech_notes_ticket_tech
      union
select null as tot
              , null                                as genNB
              ,sum(tech_notes.time_spent)          as totNB 
               ,null as gen
              , tech_notes.tech_notes_ticket_tech   as userN
              , users.dailyHours  as daily
              , users.tech_email as tEmail
           from tech_notes
         
           join new_tickets 
             on tech_notes.ticket_unique_id
              = new_tickets.ticket_unique_id
           join users
             on users.tech_user = tech_notes.tech_notes_ticket_tech
           where new_tickets.billable IS NOT NULL
            and new_tickets.ticket_status <> "new"
            and tech_notes.tech_notes_real_date = '2004-12-01'
           
       group 
             by tech_notes.tech_notes_ticket_tech
     
       union 
        select null                                as tot 
              , sum(generaltimesheet.hoursSpent)    as gen
              , null 			 as totNB
              , null			 as genNB
              , generaltimesheet.userName           as userN
              , users.dailyHours as daily
              , users.tech_email as tEmail
           from generaltimesheet
            join users
             on users.tech_user = generaltimesheet.userName
          where generaltimesheet.billable IS NOT NULL
          and generaltimesheet.generalDate = '2004-12-01'
            
       group by generaltimesheet.userName
    union 
        select null                                as totNB
               ,null as tot
               ,null as gen 
              , sum(generaltimesheet.hoursSpent)    as genNB
              , generaltimesheet.userName           as userN
              , users.dailyHours as daily
              ,users.tech_email as tEmail
           from generaltimesheet
           join users
             on users.tech_user = generaltimesheet.userName
          where generaltimesheet.billable IS NULL
          and generaltimesheet.generalDate = '2004-12-01'
           group 
             by generaltimesheet.userName
              
    
       ) as newTable
group
    by userN
     
order
    by userN
        ) as brandNew

          ) as hoursTable

) as final

Any help would be greatly appreciated!

Jon

[r937]

that query is just way too complex to try to get into

what i think you need is a LEFT OUTER JOIN such that all users are returned, whether or not they have matching rows, and then take it from there

no idea how you would combine the LEFT OUTER JOIN with that monster, though

[jreider]

yeah, I know it's a beast - it's *loosely* based upon one that you put together for me a while back, but I expanded on it to get one big total. The way I'm handling it now is just having this query in a function, and throwing each user into the function individually - not as graceful, but still works. It's not for a live page, it's just a batch job that runs at night. Thanks again for taking the time to look at it though!

Jon