trying to add another table

i'm trying add last paragraph query. query runs ignoring last part.

thank you,

select *

 

from arr_analytics.dbo.detail d

inner join arr_analytics.dbo.detail di

on d.sd_key=di.sd_key

where d.sd_tran_cde_cat in ('31')

and d.sd_ben_wir_acct_ident not ('%twr%')

and d.sd_term_name_loc = di.sd_term_name_loc

and d.md_tran_amt1 > 4999

and di.sd_tran_cde_cat in ('31')

and di.sd_ben_wir_acct_ident not ('%twr%')

and di.md_tran_amt1 > 4999

and di.dd_date between dateadd(minute, -10, d.dd_date) , dateadd(second, -5, d.dd_date)

, exists (select sd_datekey arr_analytics.dbo.detail di1

where di.sd_key = di1.sd_key

and di1.sd_tran_cde_cat in ('21')

and di1.md_tran_amt1 > 10000

and di1.dd_date between dateadd(day, -3, di.dd_date) , dateadd(second, -5, di.dd_date))

 

 

, exists (select di1.sd_key, di1.sd_datekey

from arr_analytics.dbo.detail di1

join arr_analytics.dbo.master m

on di1.sd_key = m.sm_key

where m.dm_acct_opn_dat between '06/01/2011' , '07/27/2011')

hi aciccia,

do mean if the following subquery returns empty data, whole query still have rows?

select di1.sd_key, di1.sd_datekey
arr_analytics.dbo.detail di1
join arr_analytics.dbo.master m
on di1.sd_key = m.sm_key
m.dm_acct_opn_dat between '06/01/2011' , '07/27/2011'

from query, last subquery not associated tables given in clause.

so, try to change query this:

if exists (select di1.sd_key, di1.sd_datekey arr_analytics.dbo.detail di1 join arr_analytics.dbo.master m on di1.sd_key = m.sm_key m.dm_acct_opn_dat between '06/01/2011' , '07/27/2011')

select *
from arr_analytics.dbo.detail d inner join arr_analytics.dbo.detail di
                                           on d.sd_key=di.sd_key
where d.sd_tran_cde_cat in ('31')
and d.sd_ben_wir_acct_ident not ('%twr%')
and d.sd_term_name_loc = di.sd_term_name_loc
and d.md_tran_amt1 > 4999
and di.sd_tran_cde_cat in ('31')
and di.sd_ben_wir_acct_ident not ('%twr%')
and di.md_tran_amt1 > 4999
and di.dd_date between dateadd(minute, -10, d.dd_date) , dateadd(second, -5, d.dd_date)
and exists (select sd_datekey arr_analytics.dbo.detail di1
where di.sd_key = di1.sd_key
and di1.sd_tran_cde_cat in ('21')
and di1.md_tran_amt1 > 10000
and di1.dd_date between dateadd(day, -3, di.dd_date) , dateadd(second, -5, di.dd_date))

thanks,
jerry

SQL Server  >  Data Mining