SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
Register Now and get your question answered!
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Combining two tables
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Starting Member

34 Posts

Posted - 11/21/2012 :  09:49:33  Show Profile  Reply with Quote

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

I have


Month code Budget
7 465318 300
9 465318 300


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.


Aged Yak Warrior

535 Posts

Posted - 11/22/2012 :  01:19:12  Show Profile  Reply with Quote

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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000