Better Condition - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Better Condition

Author

Topic

rds207
Posting Yak Master

198 Posts

Posted - 2010-03-31 : 19:52:11

HI
I have a join condition as below ,i dont need the join condition to work on Jobnames which are already there in dw_t_ec_sba table , but looks like by giving 'not in' , i still get the jobname in dw_t_ec_sba....

Is there any better way to ignore the Jobnames already there in dw_t_ec_sba ?

select *

FROM dbo.BATCHRECORDS B INNER JOIN
dbo.Target_BUILT_DATA V ON (
CHARINDEX(V.PHONEBUILD + '_', B.JOBNAME) > 0 OR
CHARINDEX(V.PHONEBUILD + 'Blueprint', B.JOBNAME) > 0 OR
V.PHONEBUILD = B.JOBNAME) INNER JOIN
dbo.DW_T_EC_JOB E ON E.JOB_ID = B.JOBID AND E.JOB_NAME = B.JOBNAME
WHERE (B.BATCHENV = 'Ecommander')

AND B.JobName NOT IN (SELECT EC_JOB_NAME FROM dbo.DW_T_EC_SBA)
)

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-04-01 : 02:29:53

Rewrite the query like this


select *
FROM dbo.BATCHRECORDS B 
LEFT JOIN dbo.DW_T_EC_SBA S ON B.JOBNAME = S.EC_JOB_NAME
INNER JOIN dbo.Target_BUILT_DATA V ON (
CHARINDEX(V.PHONEBUILD + '_', B.JOBNAME) > 0 OR
CHARINDEX(V.PHONEBUILD + 'Blueprint', B.JOBNAME) > 0 OR
V.PHONEBUILD = B.JOBNAME) 
INNER JOIN dbo.DW_T_EC_JOB E ON E.JOB_ID = B.JOBID AND E.JOB_NAME = B.JOBNAME
WHERE (B.BATCHENV = 'Ecommander')
AND S.EC_JOB_NAME IS NULL

Vaibhav T

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-01 : 04:55:42

[code]select *

FROM dbo.BATCHRECORDS B INNER JOIN
dbo.Target_BUILT_DATA V
ON V.PHONEBUILD = B.JOBNAME
INNER JOIN
dbo.DW_T_EC_JOB E ON E.JOB_ID = B.JOBID
AND E.JOB_NAME = B.JOBNAME
LEFT JOIN dbo.DW_T_EC_SBA
ON dbo.DW_T_EC_SBA.EC_JOB_NAME=B.JobName
WHERE (B.BATCHENV = 'Ecommander')
AND (B.JOBNAME LIKE '%' + V.PHONEBUILD + '_' + '%'
OR B.JOBNAME LIKE '%' + V.PHONEBUILD + 'Blueprint' + '%')
WHERE dbo.DW_T_EC_SBA.EC_JOB_NAME IS NULL
[/code]

AND B.JobName NOT IN (SELECT EC_JOB_NAME FROM dbo.DW_T_EC_SBA)
)
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources