how to populate values when "not found" occurs

[klkuab]

I have 2 tables (table-A & table-B). They are defined as such:

TABLE-A

DEPARTMENT
ACCOUNT

TABLE-B

DEPARTMENT
ACCOUNT
SPEND
BUDGET

TABLE-A is my driver for my query as it has all the departments and accounts, whereas TABLE-B only has records for the departments and accounts that have either a spend or a budget amount associated with it.

I need to report on all department / accounts regardless if there is a spend or budget amount, thus I know I need to do an outer join such as:

select a.department, a.account, b.spend, b.budget
from table-a a LEFT OUTER JOIN table-b b on
a.department = b.department and
a.account = b.account

my problem is HOW DO I INSERT VALUES of zero FOR SPEND & BUDGET when there isn't a record in table-b ???

many many thanks!

 

[PHV]

SELECT a.department, a.account
, CASE WHEN b.spend IS NULL THEN 0 ELSE b.spend END
, CASE WHEN b.budget IS NULL THEN 0 ELSE b.budget END
FROM table-a a LEFT OUTER JOIN table-b b ON
a.department = b.department AND
a.account = b.account

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[LKBrwnDBA]

Depending on the LANGUAGE you are using, check out the ISNULL(), NVL(), ... or similar function(s) that return a value(like '0') when the value of the column does not exist.




----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[PHV]

Another shorter (but still ANSI compliant) way:
SELECT a.department, a.account, COALESCE(b.spend,0), COALESCE(b.budget,0)
FROM table-a a LEFT OUTER JOIN table-b b ON
a.department = b.department AND
a.account = b.account

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[JarlH]

An ANSI SQL short form of the CASE expression is COALESCE.

COALESCE(b.spend, 0) means if b.spend is not null then return b.spend, else return 0.

I.e. the COALESCE expression returns the value of the first non-NULL operand, found by working from left to right, or NULL if all the operands equal NULL.

 

[r937]

by the way, [red]table-a[/red] is not a valid identifier

if your table names really have dashes in dem, you have to excape dem

"table-a" in ANSI SQL, [table-a] in SQL Server, `table-a` in MySQL, eck settera

;-)

r937.com | rudy.ca