First off sorry for the table structure it was made before I got here but here is the query I am trying...

Select
SE.Sub_Ev_Nbr,
SE.Event_Nbr,
SE.Start_Date,
SE.Start_Time,
SE.End_Time,
SE.ShowStartTime,
SE.ShowEndTime,
SE.Max_Atten_Allow,
SE.Setup_Style,
SE.Room_Name,
SE.RateDesc,
SE.Room_Charge,
SE.Day_Of_Week,
RM.Job_Cost,

SLTech."Position" As TechPositions,
SLSec."Position" As SecPositions,

Sum(SLTech.Total_Cost) As TechSum,
Sum(SLSec.Total_Cost) As SecSum
From
Events EV
Inner Join Subevent SE
On
(SE.Event_Nbr = EV.Event_Nbr)

Left Join SubLabor SLTech
On
(SLTech.Sub_Event_Nbr =
Sub_Ev_Nbr
And
SLTech.TechPositions = 'Technician')

Left Join SubLabor SLSec
On
(SLSec.Sub_Event_Nbr =
SE.Sub_Ev_Nbr
And
SLSec.SecPositions = 'Security')

Inner Join Rooms RM
On
(RM.Rm_Name = SE.Room_Name)
Where
SE.Event_Nbr = :EventNumber
And
EV.Status_Level > 0 /* Not Canceled */
Group By
SE.Sub_Ev_Nbr,
SE.Event_Nbr,
SE.Start_Date,
SE.Start_Time,
SE.End_Time,
SE.ShowStartTime,
SE.ShowEndTime,
SE.Max_Atten_Allow,
SE.Setup_Style,
SE.Room_Name,
SE.RateDesc,
SE.Room_Charge,
SE.Day_Of_Week,
SLTech."Position",
SLSec."Position",
RM.Job_Cost
Order By
SE.Start_Date,
SE.Start_Time

I know its kinda complex but we have three tables Events(Events), Rooms for the Event(Subevents), and Labor for the Event(SubLabor) and (Room).

They are all tied together by the Event_Nbr key(for the Event Total Labor) and Sub_Event_Nbr(for Labor to each Room).

There are two hard coded items that group the labor types "Technician" and "Security".

What I need is a report that shows all of the Rooms for a selected event ":EventNumber" and list two columns showing a sum of the "Technician" charges and the sum of the "Security" charges for each room. There is a one to many relation from the roooms to the labor, this is why I need to sum for "Labor = Room Key (Sub_Event_Nbr) and Labor = 'Technician' ".

The problem is that I can not use an "AND" in my joins. Is there another way?

I figured it out but thanks for listening. Here was my salution...

select
SE.Sub_Ev_Nbr,
SE.Event_Nbr,
SE.Start_Date,
SE.Start_Time,
SE.End_Time,
SE.ShowStartTime,
SE.ShowEndTime,
SE.Max_Atten_Allow,
SE.Setup_Style,
SE.Room_Name,
SE.RateDesc,
SE.Room_Charge,
(select
sum(SL.total_cost),
SL.sub_event_nbr,
SL."Position"
from
SubLabor SL

where
SL."Position" = 'Security'
and
SL.sub_event_nbr = se.sub_ev_nbr
Group By
SL.total_cost,
SL.sub_event_nbr,
SL."Position") as SecurityTotal,

(select
sum(SL.total_cost),
SL.sub_event_nbr,
SL."Position"
from
SubLabor SL

where
SL."Position" <> 'Security'
and
SL.sub_event_nbr = se.sub_ev_nbr
Group By
SL.total_cost,
SL.sub_event_nbr,
SL."Position") as TechnicianTotal,
se.labor_amount

from
subevent se Left Join events ev on
(se.event_nbr = ev.event_nbr)

where
EV.Event_Nbr = :EventNumber
AND
EV.Status_Level > 0