Combining two tables - 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 2008 Forums

Transact-SQL (2008)

Combining two tables

Author

Topic

andypgill
Starting Member

34 Posts

Posted - 2012-11-21 : 09:49:33

Hi

Can anyone help me with a query two join two tables.

I have

Bud

Month code Budget
7 465318 300
9 465318 300

Fore

Month code Forecast
5 465318 400
6 465318 100
7 465318 200

I need to combine both tables (and in this case selecting month 5 from the Fore table) however in some cases there will be a bud without a Fore and visa versa

In the example above I want the result to be.

Month code Budget Forecast
7 465318 300 400
9 465318 300 400

I have tried everything.

The problem is when there is no month 5 (my selected month) in Fore. I still need it to show the data in the Bud for that code.

Hope this makes sense.

Thanks

stepson
Aged Yak Warrior

545 Posts

Posted - 2012-11-22 : 01:19:12

Hi,

on what base do you select 400 at Forecast ?
-because is max or what ?

; with B
as (
select 7 as [Month],465318 as Code, 300 as budget union all
select 9 as [Month],465318 as Code, 300 as budget
),
F as (
select 5 as [MOnth], 465318 as Code ,400 as ForeCast union all
select 6 as [MOnth], 465318 as Code ,100 as ForeCast union all
select 7 as [MOnth], 465318 as Code ,200 as ForeCast
)

select B.*,F.foreCast
from B inner join

(select code, max(ForeCAst) as ForeCast from F
group by code )F on B.code=F.code

Ce-am pe mine am si-n dulap, cand ma-mbrac zici ca ma mut
sabinWeb

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources