Join help on Col with similar 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

SQL Server 2008 Forums

Transact-SQL (2008)

Join help on Col with similar data

Author

Topic

eljapo4
Posting Yak Master

100 Posts

Posted - 2012-08-09 : 04:45:26

Hi I'm trying to Join 2 tables. Table A has a COL called Customer_Code here's some sample data from this COL:
1000510
101470
101470
10390
10400
10420
10430

Table B has a COL called Customer_No here's some sample data from this COL:
01000510
00101470
00101470
00010390
00010400
00010420 using a LIKE also returns (54104200)
00010430

I was trying something like this:
SELECT *
FROM TABLE A A
INNER JOIN TABLE B B ON A.Customer_Code = (SELECT * FROM TABLE B WHERE B.Customer_No LIKE '%A.Customer_Code%'

but I'm getting a SQL error can anyone help me on fixing the Join between the 2 tables please?

stepson
Aged Yak Warrior

545 Posts

Posted - 2012-08-09 : 05:01:04

hi
your problem is this : from TABLE A

and join TABLE B

try this script

SELECT *
FROM [TABLE A] A
INNER JOIN [TABLE B] B ON A.Customer_Code = B.customer_no

S

eljapo4
Posting Yak Master

100 Posts

Posted - 2012-08-09 : 05:12:58

Should have been more specific but my problem was on joining the 2 Cols. this this seems to work:
SELECT *
FROM TABLE A A
INNER JOIN TABLE B B ON A.Customer_Code = CAST(B.Customer_No AS INT)

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2012-08-09 : 06:30:25

That will do an intger join. They must both be character so it would be clearer to code it as

SELECT *
FROM TABLE A A
INNER JOIN TABLE B B
ON cast(A.Customer_Code as int) = CAST(B.Customer_No AS INT)

Note - this cannot use an index and it usually causes issues because the constraint on the table will allow what is in effect duplicates - e.g. 00001004 and 0001004.
You would be better off holding the values as integers and converting for the client.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-09 : 10:15:10

unless you've non numeric data on your columns i would make them of integer datatype

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources