Join Tables with Almost Identical Column Data

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

Join Tables with Almost Identical Column Data

Author

Topic

Jlamb62480
Starting Member

10 Posts

Posted - 2014-10-29 : 18:49:57

Hello all,

This is probably a no brainer, but seeing as i=nobrain, could you please help me with selecting the data from these two tables?
The columns 'host_ext_id' have the same data BUT in 'adrmst' all data is preceeded by A0000.

table is 'adrmst'

Host External ID Address Name Address Line 1 Address Line 2 City State Postal Code
A000042401 T-3803 VC 1530401 00 WENGER ST TOPEKA KS 66609
A000042402 CO INC 960 PP TOMLIN MILL RD STATESVILLE NC 286258332
A000042403 CO INC 1420 PP BLVD GARYSBURG NC 278319748
A000042405 CO INC 1419 PP BROWN RD KISSIMMEE FL 347463415
A000042405 CO INC 962 PP COMMERCE DR VALDOSTA GA 316011206

table is 'shipment'

Shipment ID Host External ID car_move_id P_DEST_LOC_ID
42401 42401
42402 42402 SDQD_00862 TAGSDQD
42403 42403 SDQD_00863 TAGSDQD
42404 42404 SDQD_00863 TAGSDQD
42405 42405 SDQD_00863 TAGSDQD

bitsmed
Aged Yak Warrior

545 Posts

Posted - 2014-10-29 : 19:07:34

Try this:

select *
  from adrmst as a
       left outer join shipment as s
                    on 'A0000'+str(s.host)=a.host

Jlamb62480
Starting Member

10 Posts

Posted - 2014-10-29 : 19:14:01

Very close I think.... I get this error message when I attempted that script.
Error converting data type nvarchar to float.

bitsmed
Aged Yak Warrior

545 Posts

Posted - 2014-10-29 : 19:18:04

quote:
Originally posted by Jlamb62480

Very close I think.... I get this error message when I attempted that script.
Error converting data type nvarchar to float.

Remove the str() function, like this:

select *
  from adrmst as a
       left outer join shipment as s
                    on 'A0000'+s.host=a.host

Jlamb62480
Starting Member

10 Posts

Posted - 2014-10-29 : 19:25:03

By jove I think you've done it sir. Thank you!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources