Results 1 to 2 of 2
  1. #1
    Join Date
    Feb 2009
    Posts
    1

    Unanswered: Query Differences

    Could someone be nice enough to explain why loving the line below (it's bolded) changes the result set coming back?

    Query #1 (gets back 1911 rows):

    SELECT prpr.PRPR_ID, prpr.PRPR_NPI, prer.PRER_PRPR_ID, prpr2.PRPR_NAME, prcp.PRCP_FIRST_NAME,
    prcp.PRCP_LAST_NAME, prpr.PRCR_ID, prpr.PRCF_MCTR_SPEC, mctr.MCTR_DESC, prpr.PRCF_MCTR_SPEC2,
    mctr2.MCTR_DESC AS MCTR_DESC2, prpr.MCTN_ID, prad.PRAD_ADDR1, prad.PRAD_ADDR2, prad.PRAD_ADDR3,
    prad.PRAD_CITY, prad.PRAD_STATE, prad.PRAD_ZIP, prad.PRAD_PHONE, prad.PRAD_PHONE_EXT,
    prad.PRAD_FAX, prad.PRAD_FAX_EXT, prpr.PRPR_TERM_DT

    from dbo.CMC_PRPR_PROV prpr
    inner join dbo.CMC_PRAD_ADDRESS prad
    on prpr.PRPR_ID = prad.PRAD_ID and
    prad.PRAD_TYPE = 'P'
    inner join dbo.CMC_NWPR_RELATION nwpr
    on prpr.PRPR_ID = nwpr.PRPR_ID and
    nwpr.NWPR_TERM_DT > getdate() and
    nwpr.NWPR_PCP_IND = 'Y' and
    nwpr.NWPR_PFX in ('MR01' , 'MR02' , 'MP01' , 'MP11' )
    left join dbo.CMC_PRER_RELATION prer
    on nwpr.PRPR_ID = prer.PRPR_ID and
    nwpr.NWPR_TERM_DT = prer.PRER_TERM_DT and
    prer.PRER_PRPR_ENTITY = 'G'left join dbo.CMC_PRPR_PROV prpr2
    on prer.PRER_PRPR_ID = prpr2.PRPR_ID
    left join dbo.CMC_PRCP_COMM_PRAC prcp
    on prpr.PRCP_ID = prcp.PRCP_ID
    left join dbo.CMC_PRRG_REG prrg
    on prpr.PRCR_ID = prrg.PRCR_ID and
    prrg.PRRG_MCTR_TYPE = 'DE' and
    prrg.PRRG_TERM_DT > getdate()
    left join dbo.CMC_MCTR_CD_TRANS mctr
    on prpr.PRCF_MCTR_SPEC = mctr.MCTR_VALUE and
    mctr.MCTR_ENTITY = 'PRAC' and
    mctr.MCTR_TYPE = 'SPEC'
    left join dbo.CMC_MCTR_CD_TRANS mctr2
    on prpr.PRCF_MCTR_SPEC2 = mctr2.MCTR_VALUE and
    mctr2.MCTR_ENTITY = 'PRAC' and
    mctr2.MCTR_TYPE = 'SPEC'
    where prpr.PRPR_ENTITY = 'P' and
    prpr.WMDS_SEQ_NO not in( 1002 ,1111) and
    prpr.PRPR_MCTR_TYPE in ( 'MD', 'DO' ) and
    prpr.PRCF_MCTR_SPEC in ( 'IM' , 'GP' , 'FP', 'PED' , 'AM' , 'GER')

    Query #2 (gets back 1617 rows):

    SELECT prpr.PRPR_ID, prpr.PRPR_NPI, prer.PRER_PRPR_ID, prpr2.PRPR_NAME, prcp.PRCP_FIRST_NAME,
    prcp.PRCP_LAST_NAME, prpr.PRCR_ID, prpr.PRCF_MCTR_SPEC, mctr.MCTR_DESC, prpr.PRCF_MCTR_SPEC2,
    mctr2.MCTR_DESC AS MCTR_DESC2, prpr.MCTN_ID, prad.PRAD_ADDR1, prad.PRAD_ADDR2, prad.PRAD_ADDR3,
    prad.PRAD_CITY, prad.PRAD_STATE, prad.PRAD_ZIP, prad.PRAD_PHONE, prad.PRAD_PHONE_EXT,
    prad.PRAD_FAX, prad.PRAD_FAX_EXT, prpr.PRPR_TERM_DT

    from dbo.CMC_PRPR_PROV prpr
    inner join dbo.CMC_PRAD_ADDRESS prad
    on prpr.PRPR_ID = prad.PRAD_ID and
    prad.PRAD_TYPE = 'P'
    inner join dbo.CMC_NWPR_RELATION nwpr
    on prpr.PRPR_ID = nwpr.PRPR_ID and
    nwpr.NWPR_TERM_DT > getdate() and
    nwpr.NWPR_PCP_IND = 'Y' and
    nwpr.NWPR_PFX in ('MR01' , 'MR02' , 'MP01' , 'MP11' )
    left join dbo.CMC_PRER_RELATION prer
    on nwpr.PRPR_ID = prer.PRPR_ID and
    nwpr.NWPR_TERM_DT = prer.PRER_TERM_DT
    left join dbo.CMC_PRPR_PROV prpr2
    on prer.PRER_PRPR_ID = prpr2.PRPR_ID
    left join dbo.CMC_PRCP_COMM_PRAC prcp
    on prpr.PRCP_ID = prcp.PRCP_ID
    left join dbo.CMC_PRRG_REG prrg
    on prpr.PRCR_ID = prrg.PRCR_ID and
    prrg.PRRG_MCTR_TYPE = 'DE' and
    prrg.PRRG_TERM_DT > getdate()
    left join dbo.CMC_MCTR_CD_TRANS mctr
    on prpr.PRCF_MCTR_SPEC = mctr.MCTR_VALUE and
    mctr.MCTR_ENTITY = 'PRAC' and
    mctr.MCTR_TYPE = 'SPEC'
    left join dbo.CMC_MCTR_CD_TRANS mctr2
    on prpr.PRCF_MCTR_SPEC2 = mctr2.MCTR_VALUE and
    mctr2.MCTR_ENTITY = 'PRAC' and
    mctr2.MCTR_TYPE = 'SPEC'
    where prpr.PRPR_ENTITY = 'P' and
    prpr.WMDS_SEQ_NO not in( 1002 ,1111) and
    prpr.PRPR_MCTR_TYPE in ( 'MD', 'DO' ) and
    prpr.PRCF_MCTR_SPEC in ( 'IM' , 'GP' , 'FP', 'PED' , 'AM' , 'GER') AND
    prer.PRER_PRPR_ENTITY = 'G'

  2. #2
    Join Date
    May 2005
    Location
    South Africa
    Posts
    1,365
    Provided Answers: 1
    The additional condition in the ON clause will first eliminate record before doing the join
    Putting the condition in the where will eliminate the records after doing the join
    e.g.
    Code:
    -- create test data
    select * into #t1 from (select 
     1,'a' union all select 
     2,'b' union all select 
     3,'b' )t1(
    id,val)
    
    select * into #t2 from (select 
     1,'a' union all select 
     2,'b' )t1(
    id,val)
    -- end create test data
    
    -- left join with no additional conditions
    select *
    from #t1 a
    left join #t2 b
      on a.id=b.id
    
    -- id          val  id          val  
    -- ----------- ---  ----------- ---  
    --           1 a              1 a    
    --           2 b              2 b    
    --           3 b           NULL NULL 
    
    -- id=1 eliminated before the join
    select *
    from #t1 a
    left join #t2 b
      on a.id=b.id
     and b.id=2
    
    -- id          val  id          val  
    -- ----------- ---  ----------- ---  
    --           1 a           NULL NULL 
    --           2 b              2 b    
    --           3 b           NULL NULL 
    
    -- Eliminated after join
    select *
    from #t1 a
    left join #t2 b
      on a.id=b.id
    where b.id=2
    
    -- id          val  id          val  
    -- ----------- ---  ----------- ---  
    --           2 b              2 b    
    
    -- cleanup
    drop table #t1
    drop table #t2

Posting Permissions

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