Linking 2 tables - 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

Linking 2 tables

Author

Topic

rds207
Posting Yak Master

198 Posts

Posted - 2009-08-12 : 03:12:31

i have 2 tables,and i need to get the details of phone build in phone_versions table from the other batch_records table,the only thing which is common in both the tables is a part of jobname in batch_records is same as a part of phonebuild in phone_versions,
please let me know how i can link these 2 tables,the tables have huge data , attached is the excel sheet which shows sample data...

Table 1:Batch_Records
BatchRecordID JobName JobID
1013331 M76XXCFDCABAA_29JUL2009_080043pm 13233
1013288 Q8650BSDCAAPZD1540_29JUL2009_073622pm 12123
1013222 Q6085BSNACAZ4373_29JUL2009_070143pm 848343
1013313 FFA_Q6270139000s_HM11_29JUL2009_065329pm 23422
1013174 M6801BSMBRLAZ56205_29JUL2009_063831pm 433

Table2:Phone_versions
BuildID CLIENT PhoneBuild
120769 M76XXDFDCEADAD M76XXDFDCEADAD35805P
120770 M76XXCSDCBODAD M76XXCSDCBODAD35805P
120817 Q6085BSNACA Q6085BSNACA33701V
120818 M76XXCFDCCBAAD M76XXCFDCCBAAD3555900P
120822 M76XXCFDCABAAD M76XXCFDCABAAD3555009P

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-08-12 : 03:14:20

for a moment i thought he is back.

KH
[spoiler]Time is always against us[/spoiler]

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-08-12 : 03:15:10

What is your question ?

KH
[spoiler]Time is always against us[/spoiler]

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-08-12 : 03:44:26

typically you use the JOIN method to "link" the table


select *
from   Batch_Records b
       inner join Phone_versions p on b.JobName = p.PhoneBuild

KH
[spoiler]Time is always against us[/spoiler]

rds207
Posting Yak Master

198 Posts

Posted - 2009-08-12 : 11:29:28

but job name is not exactly same as phone build just a part of phone build is same job name.....

please help........

quote:
Originally posted by khtan

typically you use the JOIN method to "link" the table

select *
from Batch_Records b
inner join Phone_versions p on b.JobName = p.PhoneBuild

KH
[spoiler]Time is always against us[/spoiler]

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2009-08-12 : 11:58:22

IF you tell us (from the sample data you've posted already) what you need to get at the end we'll be in a better position to help you.

I Can see a few potential matches but they all produce different results depending on how many characters you want to match

For instance you have 4 rows in phone_versions starting M76XX.. If you were to join on the first 5 characters then you would get more results then if you were to join on the first 8 characters.

Only *you* can tell us what you need or is correct for this data.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

rds207
Posting Yak Master

198 Posts

Posted - 2009-08-12 : 12:33:23

i need 14chars of the phone build which matches with jobname..

thanks!

quote:
Originally posted by Transact Charlie

IF you tell us (from the sample data you've posted already) what you need to get at the end we'll be in a better position to help you.

I Can see a few potential matches but they all produce different results depending on how many characters you want to match

For instance you have 4 rows in phone_versions starting M76XX.. If you were to join on the first 5 characters then you would get more results then if you were to join on the first 8 characters.

Only *you* can tell us what you need or is correct for this data.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2009-08-13 : 04:06:12

OK.

Then the join should probably be


FROM
    batch_records br
    ( LEFT ) JOIN Phone_versions pv ON LEFT(pv.[PhoneBuild], 14) = br.[JobName]

Obviously choose LEFT or INNER as required.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources