Joining a query (inner/outer)

I have this current sql query which works great, but it only returns
the rows that exist in all tables.
I need to return all rows from po_cfg_Budget B and simply fill in 0 if
there are no totals.
Any help would be greatly appreciated.
select S.*,
b.Budget,
B.Description,
D.DeptID as DeptDescription,
(B.Budget - NotApproved - Approved) as AmountRemaining,
(B.Budget - Approved) as AmountApprovedRemaining
from BudgetStatus S,
(select * from po_cfg_budget) B,
(Select * from po_cfg_dept) D
where S.Years = B.BudgetYear
and S.Months = B.BudgetMonth
and S.DeptID = B.DeptID
and S.AccountID = B.AccountID
and S.DeptID = D.BudgetDeptID
order by B.DeptIDThis might work for you (passes syntax check but not actually
tested)...
select S.*,
b.Budget,
B.Description,
D.DeptID as DeptDescription,
(B.Budget - NotApproved - Approved) as AmountRemaining,
(B.Budget - Approved) as AmountApprovedRemaining
from BudgetStatus S
left outer join po_cfg_budget B on S.Years = B.BudgetYear
and S.Months = B.BudgetMonth
and S.DeptID = B.DeptID
and S.AccountID = B.AccountID
left outer join po_cfg_dept D on S.DeptID = D.BudgetDeptID
order by B.DeptID
If you have null dollar amounts you can wrap those in the ISNULL or
COALESCE function.
Bryce|||Not entirely sure what you are looking for but try this.
select S.*,
b.Budget,
B.Description,
D.DeptID as DeptDescription,
coalesce((B.Budget - NotApproved - Approved), 0) as AmountRemaining,
coalesce((B.Budget - Approved),0) as AmountApprovedRemaining
from po_cfg_budget B
left outer join BudgetStatus S
on S.Years = B.BudgetYear
and S.Months = B.BudgetMonth
and S.DeptID = B.DeptID
and S.AccountID = B.AccountID
left outer join po_cfg_dept D
on D.BudgetDeptID = S.DeptID
order by B.DeptID
The inline views that you were using were redundant.
"Dave" wrote:

> I have this current sql query which works great, but it only returns
> the rows that exist in all tables.
> I need to return all rows from po_cfg_Budget B and simply fill in 0 if
> there are no totals.
> Any help would be greatly appreciated.
> select S.*,
> b.Budget,
> B.Description,
> D.DeptID as DeptDescription,
> (B.Budget - NotApproved - Approved) as AmountRemaining,
> (B.Budget - Approved) as AmountApprovedRemaining
> from BudgetStatus S,
> (select * from po_cfg_budget) B,
> (Select * from po_cfg_dept) D
> where S.Years = B.BudgetYear
> and S.Months = B.BudgetMonth
> and S.DeptID = B.DeptID
> and S.AccountID = B.AccountID
> and S.DeptID = D.BudgetDeptID
> order by B.DeptID
>