Join tables based upon values in columns 1

[awaria]

SQL Server 2000 SP3

Need to join tables A & B, so I can add the $BudgetAmt column from Table B to Table A.

Desired resulting record set:

A.GLAcctNo, A.$NetChgBal, A.Year, A.Period, B.$BudgetAmt

Table A [ GL net chg balances by GL Acct No ]
Fields: GL Acct No |$net chg Bal | Year | period

Table B [GL budget by GL Acct No]
Fields: GL Acct No |$Budget Amt | BudgetID | period

My challenge is, how to join the tables and get the BudgetID from table B to pair with the Year in table A.?

In table A, the values for YEAR are 2011, 2010, 2009

In table B, the values for BudgetID are “BUD2011”,”Bud2010”,”Bud2009”

BudgetID 2011 should match up with YEAR 2011, BudgetID 2010 should match up with YEAR 2010, etc..

Certainly will join on GL Acct No, however, I need to match up the BudgetID in table B with the YEAR in table A to make record set join correctly.

What is the method/syntax for joining two tables based upon matching up values in columns?

Thanks,

Andrew

 

[awaria]

posted thread to wrong forum, do not know how to delete from this forum??

Andrew

 

[chuito7]

Join using A.YEARID = SUBSTRING(B.BUDGETID,4,4)

Hope this could help!

Jesus M. Cruz Narvaez
Senior IT Consultant
JT Consulting Group, Inc.
jesus@jtconsulting.com

 

[awaria]

Thank you much for the tip.

Andrew