Results 1 to 2 of 2
  1. #1
    Join Date
    Apr 2004
    Location
    Arizona
    Posts
    75

    Unanswered: Join on two seperate criterias

    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?

  2. #2
    Join Date
    Apr 2004
    Location
    Arizona
    Posts
    75

    Talking

    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •