Help with the Join - 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

Help with the Join

Author

Topic

rds207
Posting Yak Master

198 Posts

Posted - 2010-06-18 : 20:12:26

Hi

I am trying to have a join between tables batchrecords and target_built_data

Here is the query currently i have

SELECT 
B.JobName,
V.PhoneBuild
FROM  dbo.BATCHRECORDS B INNER JOIN
                  dbo.Target_BUILT_DATA V ON (
                  CHARINDEX(V.PHONEBUILD + '_', B.JOBNAME) > 0 OR
                  V.PHONEBUILD = B.JOBNAME)

Here the sample Data

Table Batchrecords:

Jobname

Core1008_M76XXTFGCHNDAD1421_17JUN2010
M76XXTFGCHNDAD1421_17JUN2010
1008_M76XXTFGCHNDAD1421
RE1008_17JUN2010

Table Target_Built_Data:

PhoneBuild

M76XXTFGCHNDAD1421
RE1008

So using the above query i get ouput



Jobname                                     phonebuild

Core1008_M76XXTFGCHNDAD1421_17JUN2010      M76XXTFGCHNDAD1421
Core1008_M76XXTFGCHNDAD1421_17JUN2010      RE1008
M76XXTFGCHNDAD1421_17JUN2010               M76XXTFGCHNDAD1421
1008_M76XXTFGCHNDAD1421                    M76XXTFGCHNDAD1421
RE1008_17JUN2010                           RE1008


Expected Output:

Jobname                                       PhoneBuild
Core1008_M76XXTFGCHNDAD1421_17JUN2010      M76XXTFGCHNDAD1421
M76XXTFGCHNDAD1421_17JUN2010               M76XXTFGCHNDAD1421
1008_M76XXTFGCHNDAD1421                    M76XXTFGCHNDAD1421
RE1008_17JUN2010                           RE1008

Could anybody please help me in acheiving the above...

pduffin
Yak Posting Veteran

68 Posts

Posted - 2010-06-18 : 23:02:25

It's matching the RE1008 against Core1008_M76XXTFGCHNDAD1421_17JUN2010 because of the re1008_ in Core1008_.
To address this I think we need to know all the possible structures of the job name.

rds207
Posting Yak Master

198 Posts

Posted - 2010-06-21 : 13:10:33

There are many types of jobnames , but i am actually looking for the one either separated by an "_" or no letters before the phonebuild ,

So as in the sample data i want the jobs which look like below ...

Core1008_M76XXTFGCHNDAD1421_17JUN2010
M76XXTFGCHNDAD1421_17JUN2010
1008_M76XXTFGCHNDAD1421

Is there a Join condition i can use to get the above?

quote:
Originally posted by pduffin

It's matching the RE1008 against Core1008_M76XXTFGCHNDAD1421_17JUN2010 because of the re1008_ in Core1008_.
To address this I think we need to know all the possible structures of the job name.

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-06-22 : 13:38:40

This?

SELECT 
B.JobName,
V.PhoneBuild
FROM  dbo.BATCHRECORDS B INNER JOIN
dbo.Target_BUILT_DATA V ON B.Jobname like '%' + V.PhoneBuild + '%'

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources