Opposite of the Query.. - 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

Opposite of the Query..

Author

Topic

rds207
Posting Yak Master

198 Posts

Posted - 2010-05-27 : 18:36:17

Hi

From the below query i get the data , from batchrecords table joining on job_id in dw_t_ec_job table , again linking it with the data from sba_built_data ,

now i need to get the data from batchrecords table which has the job_id linked to dw_t_ec_job , but not linked to sba_built_data, that means i should get jobs which are not there in the table formed by the below query ...

Could any one please help me with this....

Here is my current query:

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

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2010-05-27 : 19:21:58

This is untested
LEFT JOIN dbo.SBA_BUILT_DATA V ON

V.PHONEBUILD like '%' B.JOBNAME + '%'
WHERE (B.BATCHENV = 'Ecommander') AND
E.START_TIME >getdate()-1
and v.phonebuild is null

Jim

Everyday I learn something that somebody else already knew

rds207
Posting Yak Master

198 Posts

Posted - 2010-05-27 : 19:42:22

Thank you for the reply , what is the Join i will be using for DW_T_EC_JOB table ?

quote:
Originally posted by jimf

This is untested
LEFT JOIN dbo.SBA_BUILT_DATA V ON

V.PHONEBUILD like '%' B.JOBNAME + '%'
WHERE (B.BATCHENV = 'Ecommander') AND
E.START_TIME >getdate()-1
and v.phonebuild is null

Jim

Everyday I learn something that somebody else already knew

rds207
Posting Yak Master

198 Posts

Posted - 2010-05-27 : 19:58:20

[code]Please let me know if Further explanation is need for my question ,

Here is some sample data and expected ouptput

Table:-DW_T_EC_JOB
JOB_NAME JOB_ID
SBAM76XXDFDCDPXZD3820_2X_26MAY2010_005746am 1787657
kbharath_26MAY2010_013258pm 1787741
T8200CFACPACZD102875_26MAY2010_012006am 1788059
SBAM76XXCSDCABAZD3555A_S23_26MAY2010_025320am 1788264

Table:BatchRecords
JOB_NAME JOB_ID BatchEnv
SBAM76XXDFDCDPXZD3820_2X_26MAY2010_005746am 1787657 Ecommander
kbharath_26MAY2010_013258pm 1787741 Ecommander
T8200CFACPACZD102875_26MAY2010_012006am 1788059 Ecommander
SBAM76XXCSDCABAZD3555A_S23_26MAY2010_025320am 1788264 Ecommander
Q8650BFDCANWZA222101_26MAY2010_025905am null Intergraph

Table:-SBA_BUILT_DATA
PhoneBuild Subsystem
SBAM76XXCSDCABAZD3555A_S23 MSM7600
SBAM76XXDFDCDPXZD3820_2X MSM7600

Expected Output
Table 1
JOB_NAME JOB_ID PhoneBuild Subsystem JobType
kbharath_26MAY2010_013258pm 1787741 NULL NULL NULL
T8200CFACPACZD102875_26MAY2010_012006am 1788059 NULL NULL NULL

[/code]

The below did not work , could any body please help me in acheving the above expected output ?

The other option what i tried is to left Join on the table dw_t_ec_sba ,

[code]

SELECT *
FROM [ASWTrans].[dbo].[DW_T_EC_SBA] A left join DW_T_EC_JOB B on A.EC_JOB_NAME = B.JOB_NAME
where b.START_TIME > GETDATE()-1 AND A.EC_JOB_ID IS NULL

[/code]

DW_T_EC_SBA is the table which is formed by working with the Join condition ( the query which i have given in my first post for this topic) , so i am trying to have a left join with that table so that it gives all records from DW_T_EC_JOB which are not there in DW_T_EC_SBA , but the above left join query also does NOT seem to work , i mean i am not getting any records , is there anything wrong in the above query ?

Please Help ...Any suggestions are higly aprreciated..........

tmaiden
Yak Posting Veteran

86 Posts

Posted - 2010-05-28 : 14:54:48

A.EC_JOB_ID wouldn't be NULL
When using a left outer join the left table (DW_T_EC_JOB) would be null where it doesnt join
Try
where b.START_TIME > GETDATE()-1 AND B.JOB_NAME IS NULL

rds207
Posting Yak Master

198 Posts

Posted - 2010-05-31 : 17:54:24

this does not seem to work with the SSIS jobs for the insert of data into these tables i have ....

Please suggest how do i get the expected output given above...

quote:
Originally posted by tmaiden

A.EC_JOB_ID wouldn't be NULL
When using a left outer join the left table (DW_T_EC_JOB) would be null where it doesnt join
Try
where b.START_TIME > GETDATE()-1 AND B.JOB_NAME IS NULL

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources