Join on 2 tables where values are different

Question

Post reply

Join on 2 tables where values are different

ByronOne

SSCarpal Tunnel

Points: 4656
More actions
April 3, 2013 at 5:18 am

#270587

Hi everyone
I have 2 tables in SQL and i would like to join them together however the fields I wish to join on have slightly different information.
Table1 has the field Region and Table2 has the field Region. However Table2s Region field contains a * either side of the string. So in Table1 it would appear as NORTH whilst in Table2 as *NORTH*.
Is there a way I can join the 2 tables when they differ in this way?
Thanks in advance.
BO

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply

Mike John SSCertifiable Points: 7186 More actions · Answer 1

Two quick options

either

select ..

from a

inner join b on a.col1 = '"' + b.col1 + '"'

or

select ..

from a

inner join b on replace(a.col1,'"','') = b.col1

but either will probably not perform too well. so it depends how often you need to do this.

If possible it would be better to update the data in one table to remove the quotes.

Mike John

ByronOne SSCarpal Tunnel Points: 4656 More actions · Answer 2

Cheers for the quick update...

Actually they are stars * rather than quotes "" Tried the same logic but it didn't seem to like it - no error but no join either...

Any pointers?

Mike John SSCertifiable Points: 7186 More actions · Answer 3

Sorry for misreading the stars/quotes - eyes must be getting old!

No reason I can think of for one or the other option not working - unless you actually have leading or trailing spaces in either as well as the stars

try something like

select '[' + col + ']' from a to see if you actually have extra characters in there.

Mike John

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 4

SELECT *

FROM Table1 t1

INNER JOIN Table2 t2

ON t2.Region = '*'+t1.Region+'*'

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

ByronOne SSCarpal Tunnel Points: 4656 More actions · Answer 5

These solutions are both great guys - thanks so much!!

One other thing, what if the region in Table2 did not only have multiplication (star) signs either side but also other text? Would it be possible to find the region within the text and then link to Table1?

Thanks again!!

BO

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 6

Use the LIKE operator:

SELECT *

FROM Table1 t1

INNER JOIN Table2 t2

ON t2.Region LIKE '*'+t1.Region+'*%'

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

ByronOne SSCarpal Tunnel Points: 4656 More actions · Answer 7

Cheers for this. I actually got it the wrong way round though!!

The table with the multiplication signs round the REGION does not have the extra text its the other table...

ByronOne SSCarpal Tunnel Points: 4656 More actions · Answer 8

Ignore my last message, I'm being an idiot as usual...

ByronOne SSCarpal Tunnel Points: 4656 More actions · Answer 9

ByronOne

SSCarpal Tunnel

Points: 4656

April 3, 2013 at 8:22 am

#1603189

Thanks both for your help!!

BO

ByronOne SSCarpal Tunnel Points: 4656 More actions · Answer 10

The following code only joins on the first four records in #TableRegionOther when I want it to bring back everything apart from (' SouthWest ', 19)...

Any clues as to what I might be doing wrong?

Thanks

BO

create table #TableRegion

(

[Region] varchar(10)

)

insert into #TableRegion (Region)

values ('*North*'),

('*East*'),

('*South*'),

('*West*');

create table #TableRegionOther

(

[Region] varchar(25),

[Sales] int

)

insert into #TableRegionOther (Region, Sales)

values ('North', 123),

('East', 43),

('South', 765),

('West', 9364),

('PHC North', 4),

('East TRC', 4356),

(' SouthWest ', 19),

('dd South rts ', 234);

SELECT *

FROM #TableRegion t1

inner JOIN #TableRegionOther t2

ON t1.Region like '*%'+t2.Region+'%*'

Luis Cazares SSC Guru Points: 183698 More actions · Answer 11

The queries will be inefficient, unless you correct your data.

Here's one option, but you'll have a problem with "SouthWest"

SELECT *

FROM #TableRegion t1

FULL JOIN #TableRegionOther t2

ON CHARINDEX( SUBSTRING(t1.Region, 2, 4), t2.Region) > 0

And another way:

SELECT *

FROM #TableRegion t1

FULL JOIN #TableRegionOther t2

ON t2.Region LIKE '%' + REPLACE( t1.Region, '*', '') + '%'

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

ByronOne SSCarpal Tunnel Points: 4656 More actions · Answer 12

Cheers Luis

This works great but like you say the data will need a little tweaking...