Inner Join on Varchar fields - 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

SQL Server 2012 Forums

Transact-SQL (2012)

Inner Join on Varchar fields

Author

Topic

Ciupaz
Posting Yak Master

232 Posts

Posted - 2013-01-10 : 08:08:46

Hello all,
I have 2 tables with a Varchar(100) fields, something like these:

TableA
TableA_ID - Value - Description - Value2
1 - 23 - 'City1 Gr. 1' - 300
2 - 25 - 'City1 Gr. 2' - 340
3 - 33 - 'City2 Gr. 1' - 320
4 - 13 - 'City3 Gr. 1' - 308

TableB
TableB_ID - Value - Description - Value2
1 - 63 - 'City1 Gr. 1' - 500
2 - 75 - 'City1 Gr. 2' - 540
3 - 63 - 'City2 Gr. 1' - 520
4 - 83 - 'City3 Gr. 1' - 508

Is it possible join them with the varchar fields?

Thanks in advance.

Luigi

PS
I'm using SQL Server 2008 R2

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2013-01-10 : 08:58:47

Yes.

==========================================
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.

Ciupaz
Posting Yak Master

232 Posts

Posted - 2013-01-10 : 09:26:25

It does not work, maybe for culture o accent impostations?

Luigi

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2013-01-10 : 13:26:28

Are you saying the varchar COLUMNs have different collations?

wholesalenfljerseyssale
Starting Member

9 Posts

Posted - 2013-01-11 : 01:17:14

unspammed

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-01-11 : 03:02:48

quote:
Originally posted by Ciupaz

It does not work, maybe for culture o accent impostations?

Luigi

first check if contents are exactly same without any additional non printable characters

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

Ciupaz
Posting Yak Master

232 Posts

Posted - 2013-01-11 : 03:06:09

Yes, they have, I think, differente collations.

Luigi

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-01-11 : 03:09:46

then overide them using COLLATE in join like


SELECT *
FROM tablA a
JOIN TableB b
ON b.Description COLLATE database_default = a.Description COLLATE database_default

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

Ciupaz
Posting Yak Master

232 Posts

Posted - 2013-01-11 : 03:33:32

This works. Thank you very much Visakh.

Luigi

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-01-11 : 03:37:38

welcome

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources