locked
possible to use left join in the existing query like this? RRS feed

  • Question

  • Hi, I have an existing application which executes a query like the below, is there anyway I can use left join other tables without changing too much.

    SELECT DISTINCT VIQSESSION.CourtType, VIQSESSION.SESSIONNAME, VIQSESSION.COURTROOMNUMBER, VIQSESSION.JUDGELASTNAME, VIQSESSION.JUDGEFIRSTNAME, HEARINGPEOPLE.LASTNAME,TRANDEF.NodeID, TRANDEF.VIQDATETIME, TRANDEF.CASECODE, TRANDEF.COURTCASENUMBER, TRANDEF.TRANDEFID,VIQCASE.SEALEDCASE, TRANDEF.MAINTRANDEFID,TRANDEF.PartialDeleted 
                                 FROM VIQSESSION, TRANDEF,HearingPeople, VIQCASE WITH (NOLOCK) WHERE VIQSESSION.SESSIONNAME = TRANDEF.SESSIONNAME
                                 AND TRANDEF.CODEFENDANT <> 'C'
    							 AND TRANDEF.TRANDEFID = HearingPeople.TrandefID
    							 AND HearingPeople.Role = 15
                                 AND VIQSESSION.NodeID = TRANDEF.NodeID
                                 AND VIQCASE.SESSIONNAME = TRANDEF.SESSIONNAME
                                 AND VIQCASE.CASECODE = TRANDEF.CASECODE
                                 AND VIQCASE.NodeID = TRANDEF.NodeID
                                 AND VIQCASE.RECALL = 0
                                 AND VIQCASE.VIQCOMMENT <> 'I'

    Friday, February 10, 2017 8:33 PM

Answers

  • Here is a start:

    SELECT DISTINCT VIQSESSION.CourtType
     , VIQSESSION.SESSIONNAME
     , VIQSESSION.COURTROOMNUMBER
     , VIQSESSION.JUDGELASTNAME
     , VIQSESSION.JUDGEFIRSTNAME
     , HEARINGPEOPLE.LASTNAME
     ,TRANDEF.NodeID
     , TRANDEF.VIQDATETIME
     , TRANDEF.CASECODE
     , TRANDEF.COURTCASENUMBER
     , TRANDEF.TRANDEFID
     ,VIQCASE.SEALEDCASE
     , TRANDEF.MAINTRANDEFID
     ,TRANDEF.PartialDeleted 
      FROM VIQSESSION
     left outer join TRANDEF
     on VIQSESSION.SESSIONNAME = TRANDEF.SESSIONNAME
     AND VIQSESSION.NodeID = TRANDEF.NodeID
     AND TRANDEF.CODEFENDANT <> 'C'
     left outer join HearingPeople
     ON TRANDEF.TRANDEFID = HearingPeople.TrandefID
     AND TRANDEF.TRANDEFID = HearingPeople.TrandefID
     AND HearingPeople.Role = 15
     left outer join  VIQCASE WITH (NOLOCK) 
       ON VIQCASE.SESSIONNAME = TRANDEF.SESSIONNAME
                AND VIQCASE.CASECODE = TRANDEF.CASECODE
                AND VIQCASE.NodeID = TRANDEF.NodeID
                AND VIQCASE.RECALL = 0
                AND VIQCASE.VIQCOMMENT <> 'I'


    Russel Loski, MCSE Data Platform/Business Intelligence Twitter: @sqlmovers; blog: www.sqlmovers.com

    • Marked as answer by sdnd2000 Saturday, February 11, 2017 2:13 AM
    Friday, February 10, 2017 8:48 PM