Better Join 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 Join condition

Author

Topic

rds207
Posting Yak Master

198 Posts

Posted - 2010-05-26 : 19:21:28

The below query gives me all the Jobnames which have an Jobid linked to
dbo.DW_T_EC_JOB table and dbo.BATCHRECORDS tables along with
dbo.SBA_BUILT_DATA data which falls in condition
dbo.SBA_BUILT_DATA V ON (
CHARINDEX(V.PHONEBUILD + '_', B.JOBNAME) > 0 OR
V.PHONEBUILD = B.JOBNAME)

But i need all the data from dbo.DW_T_EC_JOB and dbo.BATCHRECORDS
having the data as all NULL from the table dbo.SBA_BUILT_DATA if the condition is not satisfied

That means i need to select all the jobs in BatchRecords and dbo.DW_T_EC_JOB tables and combine it with dbo.SBA_BUILT_DATA , if the condition satisfies get the data from dbo.SBA_BUILT_DATA , if not just make it as NULLS.....

SELECT 
B.BatchQueueName,
E.EC_JOB_IND,
E.JOB_NAME,
V.PhoneBuild,
V.subsystem,
B.BatchENV,
B.BatchRecordID,
V.BuildID,
V.Built_DT,
B.STARTTIME,
B.EndTime,
B.JobStatus,
V.Site,
JOB_TYPE = 'SBA'
FROM  dbo.DW_T_EC_JOB E join dbo.BATCHRECORDS B ON E.JOB_ID = B.JOBID AND E.JOB_NAME = B.JOBNAME
INNER JOIN        dbo.SBA_BUILT_DATA V ON (
                  CHARINDEX(V.PHONEBUILD + '_', B.JOBNAME) > 0 OR
                  V.PHONEBUILD = B.JOBNAME)                   
WHERE     (B.BATCHENV = 'Ecommander') AND
E.START_TIME >getdate()-1

naveengopinathasari
Yak Posting Veteran

60 Posts

Posted - 2010-05-27 : 01:11:05

SELECT
B.BatchQueueName,
E.EC_JOB_IND,
E.JOB_NAME,
V.PhoneBuild,
V.subsystem,
B.BatchENV,
B.BatchRecordID,
V.BuildID,
V.Built_DT,
B.STARTTIME,
B.EndTime,
B.JobStatus,
V.Site
FROM dbo.DW_T_EC_JOB E INNER JOIN dbo.BATCHRECORDS B
ON E.JOB_ID = B.JOBID AND E.JOB_NAME = B.JOBNAME
LEFT OUTER JOIN dbo.SBA_BUILT_DATA V
ON (CHARINDEX(V.PHONEBUILD + '_', B.JOBNAME) > 0 OR
V.PHONEBUILD = B.JOBNAME)
WHERE (B.BATCHENV = 'Ecommander') AND
E.START_TIME >getdate()-1

I have not tested this query.
I have only changed the INNER JOIN to LEFT OUTER JOIN on Table dbo.SBA_BUILT_DATA

Let me know if any issues.

Lets unLearn

rds207
Posting Yak Master

198 Posts

Posted - 2010-05-27 : 13:51:52

Thank you , This Worked !

quote:
Originally posted by naveengopinathasari

SELECT
B.BatchQueueName,
E.EC_JOB_IND,
E.JOB_NAME,
V.PhoneBuild,
V.subsystem,
B.BatchENV,
B.BatchRecordID,
V.BuildID,
V.Built_DT,
B.STARTTIME,
B.EndTime,
B.JobStatus,
V.Site
FROM dbo.DW_T_EC_JOB E INNER JOIN dbo.BATCHRECORDS B
ON E.JOB_ID = B.JOBID AND E.JOB_NAME = B.JOBNAME
LEFT OUTER JOIN dbo.SBA_BUILT_DATA V
ON (CHARINDEX(V.PHONEBUILD + '_', B.JOBNAME) > 0 OR
V.PHONEBUILD = B.JOBNAME)
WHERE (B.BATCHENV = 'Ecommander') AND
E.START_TIME >getdate()-1

I have not tested this query.
I have only changed the INNER JOIN to LEFT OUTER JOIN on Table dbo.SBA_BUILT_DATA

Let me know if any issues.

Lets unLearn

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources