Adding a Multiplies joins

Question

I have a problema with this proc.

if @p_func = '1'
begin
SELECT
    isNull(ts.num_reserva,0) as num_reserva
    ,ts.des_dia
    ,ts.des_mes
    ,ts.des_ano
    ,ts.num_sala
    ,th.des_hora_inil
    ,th.des_hora_finl
    ,ts.des_nome
    ,ts.des_rama
    --,ss.des_sala
    ,ts.num_cent_cust
    ,ts.des_tipo
    ,ts.num_pess
    ,ts.des_text
    ,ts.des_conf
    ,ts.dat_manu
    ,ts.des_logi_manu
    ,tu.nom_comp
    ,edit = '<img src="images/bt_edit.gif" style=cursor:hand border=0 onclick=manu("pagi_agen_sala_edit.aspx?func=U&des_dia='+ltrim(rtrim(@p_des_dia))+'&des_mes='+ltrim(rtrim(@p_des_mes))+'&des_ano='+ltrim(rtrim(@p_des_ano))+'&num_sala='+ltrim(rtrim(convert(varchar(10),@p_num_sala)))+'&des_hora_inil='+ltrim(rtrim(th.des_hora_inil))+'&des_hora_finl='+ltrim(rtrim(th.des_hora_finl))+'")>'
    ,dele = '<img src="images/bt_excl.gif" style=cursor:hand border=0 onclick=manu("pagi_agen_sala_edit.aspx?func=D&des_dia='+ltrim(rtrim(@p_des_dia))+'&des_mes='+ltrim(rtrim(@p_des_mes))+'&des_ano='+ltrim(rtrim(@p_des_ano))+'&num_sala='+ltrim(rtrim(convert(varchar(10),@p_num_sala)))+'&des_hora_inil='+ltrim(rtrim(th.des_hora_inil))+'&des_hora_finl='+ltrim(rtrim(th.des_hora_finl))+'")>'
FROM    tbl_agen_sala_hora  th
LEFT JOIN tbl_agen_sala ts
ON  th.des_hora_inil = ts.des_hora_inil
AND th.des_hora_finl = ts.des_hora_finl
AND ts.des_dia      =  @p_des_dia
AND ts.des_mes      =  @p_des_mes
AND ts.des_ano      =  @p_des_ano
AND ((ts.num_sala   =  @p_num_sala) OR (@p_num_sala = 0))
AND ts.num_sala     =  @p_num_sala
LEFT JOIN TBL_INTR_USUA tu
on tu.des_logi=ts.des_logi_manu
WHERE th.des_hora_inil >= @p_des_hora_inil 
AND th.des_hora_inil < @p_des_hora_finl
AND th.des_hora_finl > @p_des_hora_inil
AND th.des_hora_inil < @p_des_hora_finl


ORDER BY th.des_hora_inil, th.des_hora_finl

end

I have to add another inner join in this proc but I have Already tried to put in every where.

This inner joins that a I have to ADD:

INNER JOIN tbl_agen_sala_sala ss
on ts.num_sala = ss.num_sala

Someone can help me

Answer 1

You need to add it as a left join, because the conditions are on the second table in the existing left join. So this FROM clause should work:

FROM tbl_agen_sala_hora  thLEFT JOIN
     tbl_agen_sala ts
     ON th.des_hora_inil = ts.des_hora_inil AND
        th.des_hora_finl = ts.des_hora_finl AND
        ts.des_dia      =  @p_des_dia AND
        ts.des_mes      =  @p_des_mes AND
        ts.des_ano      =  @p_des_ano AND
        ((ts.num_sala   =  @p_num_sala) OR (@p_num_sala = 0)) AND
        ts.num_sala     =  @p_num_sala LEFT JOIN
     TBL_INTR_USUA tu
     ON tu.des_logi = ts.des_logi_manu LEFT JOIN
     tbl_agen_sala_sala ss
     ON ts.num_sala = ss.num_sala