Need all Rows:Outer Join + WHERE

Hi, I would appreciate help on the following query I got stuck with;

I've got 2 tables "AccountList" and "PeriodBalance";

I need to return all the accounts form "AccountList" and their balance form "PeriodBalance";

The user will select a period for which the balances should be returned, if there is no balance for the period... 0 should be returned. "PeriodBalance" would not have an entry if there is no balance for the period.

There is only 3 possible periods (1, 2 or 3)

And there is only one balance per period per account;

TABEL 1: AccountList:

AccNo

100

200

300

TABLE 2: PeriodBalance:

AccNo PerID PerBal

100 1 1000

100 2 1750

100 3 1800

300 1 3200

300 3 3500

This is what is what I need returned by the query, assuming we are selecting PerID 2: (WHERE PerID=2)

AccNo PerID PerBal

100 2 1750

200 2 0

300 2 0

I've included ISNULL(PerID,4) = 4 in my WHERE clause...this worked for returning AccNo 200.

My problem is AccNo 300 in not NULL....it has values, just not for the selected PerID...so how do I get AccNo 300 to be included in my Query result?

This is my SQL Query:

SELECT AccountList.AccNo, PeriodBalance.PerID, PeriodBalance.PerBal

FROM AccountList LEFT OUTER JOIN

PeriodBalance ON AccountList.AccNo = PeriodBalance.AccNo

WHERE PeriodBalance.PerID = 2 OR ISNULL(PerID,4) = 4

Thanks in advance;

You should move the filter expression from the "where" clause to the "join".

...

on a.AccNo = p.AccNo and (p.PerID = 2 or p.PerID is null)

AMB

|||

Try this Query

select accountlst.accno,per.perid,per.perbal from
accountlst
left outer join
(select * from PeriodBalance where perid=2) per
on accountlst .accno = per.accno

her u get Perid as null. For this u can replace Per.Perid with the paramter for select perid

declare @.perid int

set @.perid = 2

select accountlst.accno,@.perid,per.perbal from
accountlst
left outer join
(select * from PeriodBalance where perid=@.perid) per
on accountlst .accno = per.accno

|||

Thank you very much Hunchback! That worked just great!