Join tables based upon values in columns

[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 | periodMy

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, 2009In 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?
=========================================================
select a.GLACCT,a.$netchg,a.year,a.period,b.budgetAmt
from TableA a
inner join TableB b
on a.GLAcct = b.GLAcct
and ?????
How to say grab B.BudgetAmt value, where a.year = 2011 and b.budgetID = "bud2011"
and also grab b.BudgetAmt where a.year = 2010 and b.BudgetID = "Bud2010"

Appreciate your input.

Thanks,Andrew

 

[johnherman]

Do your own homework.

-------------------------
The trouble with doing something right the first time is that nobody appreciates how difficult it was - Steven Wright

 

[gmmastros]

You can hard code some data and use that for your join.

select  a.GLACCT,a.$netchg,a.year,a.period,b.budgetAmt
from TableA a
inner join TableB b
on a.GLAcct = b.GLAcct
and [!]'Bud' + Convert(VarChar(4), A.Year) = b.BudgetId[/!]

Please be aware that when you do this, SQL won't be able to effectively use indexes to speed up this query, so your performance may suffer.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[gmmastros]

John Herman,

Can you please explain for me why you think this is a home work assignment? awaria has posted several questions recently and none of them appear to be home work assignments to me.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[markros]

Why do you store extra unneeded info 'Bud' in the column? Is there any specific reason for this except for making queries more difficult to write and not able to use an index?

PluralSight Learning Library

 

[awaria]

What I did was strip out the "Bud" prefix on the [BudgetID] field and now the value in BudgetID in Table B will equal the value for YEAR in Table A. [2011].

Not as effeiciently as you illustrated above.

Thanks for your help.

Andrew