CODEWITHSUNDEEP
sql-serverleft-join
MrVimes
MrVimes

Reputation: 3312

Is this the only way to filter the right table in a left outer join?

I have customer balances stored in their own table. the customer balances table gets a new set of records every day (reflecting the balance that day) but contains balances for other days (yyyy-mm-dd). I wanted to get all UK customers from accountinformation and their balances yesterday from balances. I wanted to include rows from accountinformation even where there is no corresponding record (for yesterday) in balances... 

select firstname,lastname,accountnumber,balance from accountinformation i 
left outer join balances b 
on i.accountnumber = b.account
where country = 'UK' and status = 'OPEN'
and (b.date = '2014-04-10' or b.date is null)

... it did not satisfy the requirement to show rows from accountinformation if there is no corresponding row in balances. I had to write the query like this...

select firstname,lastname,accountnumber,balance from accountinformation i 
left outer join (select * from balances where date = '2014-04-10') b 
on i.accountnumber = b.account
where country = 'UK' and status = 'OPEN'

.. to get the desired behavour. In the interests of correctness I want to know if there is a more correct way to filter the left table in a left outer join?

Upvotes: 0

Views: 47

Answers (1)

Rob White
Rob White

Reputation: 1002

you might be able to do

select firstname,lastname,accountnumber,balance from accountinformation i 
left outer join balances b 
on i.accountnumber = b.account and b.date = '2014-04-10'
where country = 'UK' and status = 'OPEN'

Upvotes: 2

Related Questions