Linking on 2 differing columns

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 on 2 differing columns

Author

Topic

icw
Constraint Violating Yak Guru

378 Posts

Posted - 2007-06-12 : 10:22:43

Hi I have two tables I want to link
On table1 the column is nvarchar (length 53) the value might be XXX0123 on the second table the column is varchar (length 80) and the value might be 123.
The important thing for this company is that 'XXX0123' is the same as '123' But it isn't always going to be the last 3 characters because the first table might be 'XXX0012' and the second value would be '12'

I just want to say SELECT column1, column2 from table1, table2 WHERE
(the two are linked)

hope that makes sense.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-06-12 : 10:38:07

Try this

Select T1.column1,T2.column2 from table1 T1 inner join table2 T2 on right(T1.column1)=T2.columns2

Madhivanan

Failing to plan is Planning to fail

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-06-12 : 10:41:35

[code]Select T1.column1,T2.column2
from table1 T1 inner join table2 T2
on T1.Column1 like '%' + T2.columns2 + '%'[/code]

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

icw
Constraint Violating Yak Guru

378 Posts

Posted - 2007-06-12 : 10:42:17

Thanks
just tried this-
select [Dlr Code], Dealer_code from ['Data Only$']
inner join Composite on right([Dlr Code])=Dealer_code

Get an error saying the RIGHT function requires 2 arguments

icw
Constraint Violating Yak Guru

378 Posts

Posted - 2007-06-12 : 10:48:25

And I don't thing the expression with "like" will work because 'XXX0123' could be like '23' and '123'

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-06-12 : 10:55:46

One weird solution will be:

Select T1.column1,T2.column2 
from table1 T1 inner join table2 T2 
on Right(T1.Column1,  datalength(T2.columns2)) = T2.columns2

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-06-12 : 10:55:55

That should be

select [Dlr Code], Dealer_code from ['Data Only$']
inner join Composite on right([Dlr Code],3)=Dealer_code

Madhivanan

Failing to plan is Planning to fail

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-06-12 : 10:57:42

My previous reply was based on this
<<
Get an error saying the RIGHT function requires 2 arguments
>>

Madhivanan

Failing to plan is Planning to fail

icw
Constraint Violating Yak Guru

378 Posts

Posted - 2007-06-12 : 11:04:14

"select [Dlr Code], Dealer_code from ['Data Only$']
inner join Composite on right([Dlr Code],3)=Dealer_code"

Thanks but...

- It isn't always going to be the last three, it might be the last 2,3 or 4 characters.

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-06-12 : 11:13:53

Have you tried my solution?

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-06-12 : 11:18:41

quote:
Originally posted by harsh_athalye

One weird solution will be:

Select T1.column1,T2.column2
from table1 T1 inner join table2 T2
on Right(T1.Column1, datalength(T2.columns2)) = T2.columns2

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

Isnt Len enough?

Madhivanan

Failing to plan is Planning to fail

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-06-12 : 11:30:34

Yeah, normally it should be fine. But what if user has spaces as well in the column value?

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

icw
Constraint Violating Yak Guru

378 Posts

Posted - 2007-06-12 : 11:40:24

I have just tried it.
It is still querying, after about 2 mins. Think I might stop it if it doesn't come up with something soon.

icw
Constraint Violating Yak Guru

378 Posts

Posted - 2007-06-12 : 12:05:12

Crashed the SQl server!
Any other suggestions gratefully received.

many thanks

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-06-13 : 08:49:31

As the tables dont have proper relational key, it cant make use of index
How many rows are there in those tables?

Madhivanan

Failing to plan is Planning to fail

icw
Constraint Violating Yak Guru

378 Posts

Posted - 2007-06-13 : 14:16:11

sorry about delay.
1 of the tables has 1,500,000 rows the other has about 75,000

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-06-13 : 15:14:11

You really, really should try to fix this design .... your data should be broken out into multiple columns, then it can use indexes and be very quick, and your SQL is very, very simple. Otherwise, you don't have data integrity, your sql will be more complicated, and it will be very, very slow.

- Jeff
http://weblogs.sqlteam.com/JeffS

icw
Constraint Violating Yak Guru

378 Posts

Posted - 2007-06-14 : 08:18:26

Thanks, I Appreciate everyone's help. I will make the case for design change.

ICW

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources