Join on middle of row - 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 2000 Forums

SQL Server Development (2000)

Join on middle of row

Author

Topic

duhaas
Constraint Violating Yak Guru

310 Posts

Posted - 2006-09-12 : 17:03:48

I have a situation where I want to join two colums, both called accountnumber, but I want to join them on the charecters 3-8 of one of the two accountnumbers. Example:

Table 1

acctnumber = 1601804800F

table 2

acctnumber = 018048

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-09-12 : 17:12:18

JOIN ...
ON SUBSTRING(a1.accountnumber, 3, 6) = SUBSTRING(a2.accountnumber, 3, 6)

Tara Kizer

duhaas
Constraint Violating Yak Guru

310 Posts

Posted - 2006-09-12 : 17:25:30

Thanks for your help, I must have syntax wrong somewhere:

select g.ACCTNO, r.Acctnumber
from Accounts as g, goldreview as r
join accounts on substring(g.ACCTNO,3,6) = substring(r.Acctnumber,3,6)

The column prefix 'g' does not match with a table name or alias name used in the query.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-09-12 : 17:26:59

You are combining the old join syntax with the new join syntax. Here's how it should look using the new join syntax:

select g.ACCTNO, r.Acctnumber
from Accounts g
inner join goldreview r
on substring(g.ACCTNO,3,6) = substring(r.Acctnumber,3,6)

Tara Kizer

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-09-13 : 10:00:43

I think the questioner wants to take substring from only one table's column

select g.ACCTNO, r.Acctnumber
from Accounts T1
inner join goldreview T2
on substring(T1.ACCTNO,3,6) = t2.Acctnumber

Madhivanan

Failing to plan is Planning to fail

duhaas
Constraint Violating Yak Guru

310 Posts

Posted - 2006-09-13 : 10:23:40

Appreciate the followup, i made the adjustment myself, but love the assistance, I might not of phrased the question well

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources