SLTech."Position" As TechPositions,
SLSec."Position" As SecPositions,
Sum(SLTech.Total_Cost) As TechSum,
Sum(SLSec.Total_Cost) As SecSum
Inner Join Subevent SE
(SE.Event_Nbr = EV.Event_Nbr)
Left Join SubLabor SLTech
SLTech.TechPositions = 'Technician')
Left Join SubLabor SLSec
SLSec.SecPositions = 'Security')
Inner Join Rooms RM
(RM.Rm_Name = SE.Room_Name)
SE.Event_Nbr = :EventNumber
EV.Status_Level > 0 /* Not Canceled */
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?