declare @Table1 table (Id int, SiteNbr int, acctNum int, amt numeric(9, 2))
insert into @Table1
	select 1, 34, 1903, 4.00

declare @Table2 table (Id int, SiteNbr int, acctNum int, amt numeric(9, 2) )
insert into @Table2 
	select 1, 34, 1904, 12.00 union all
	select 2, 34, 1903, 5.00 

declare @Table3 table (Id int, acctNum int, acctDesc varchar(75))
insert into @Table3
	select 1, 1903, 'Maintenance' union all
	select 2, 1904, 'Meals' union all 
	select 3, 1905, 'Wages'

select	ttt.acctDesc, 
	isnull(tt.amt, 0.00) as [Budget1], 
	isnull(t.amt, 0.00) as [Budget2]
from	@Table3 ttt
left
join	@Table2 tt on
	ttt.Id = tt.Id
left
join	@Table1 t on
	ttt.Id = t.Id
order
by	1

quote:

---

Originally posted by rc1138

Hi Nathan,

Thanks for the reply

The tables are already created and populated - What my question pretty much was how to format the query to make my output look like this

acctDesc Budget1 Budget2
Maintenance 0.00 5.00
Meals 4.00 12.00
Wages 0.00 0.00

Based on the 3 tables mentioned in the question

---

SELECT t3.acctDesc,
SUM(CASE WHEN Category='Budget1' THEN amt ELSE 0.00 END) AS Budget1,
SUM(CASE WHEN Category='Budget2' THEN amt ELSE 0.00 END) AS Budget2
FROM Table3 t3
LEFT JOIN (SELECT siteNbr,acctNum,amt,'Budget1' AS Category
           FROM Table1
           UNION ALL
           SELECT siteNbr,acctNum,amt,'Budget2'
           FROM table2) t
ON t.acctNum=t3.acctNum
GROUP BY t3.acctDesc