SQL Right outer join with no match in right table

sql-server-2008

Solution

Notice where the conditions for the outer table (r) go - not in the WHERE clause (which converts your outer join to an inner join) but rather in the ON clause.

select
 u.id, u.fullname,
 r.*
from
 users as u
left outer join
 rapports as r
on
 u.id = r.userid
 and (r.closed = 0 
   or CONVERT(varchar, r.periodstart, 112) = convert(varchar, GETDATE(), 112))
where
 u.active = 1
order by
 u.fullname;

However this is much better:

select
 u.id, u.fullname,
 r.*
from
 users as u
left outer join
 rapports as r
on
 u.id = r.userid
 and (r.closed = 0 or CONVERT(DATE, r.periodstart) = CONVERT(DATE, GETDATE()))
where
 u.active = 1
order by
 u.fullname;

Problem

Is it anyhow possible to do a SQL query where you take some info from left table and all info from right table matching the conditions. BUT if there is no records matching the conditions in the right table, it should still show the full record, but filled with a lot of *NULL*s At the moment, I have come to this: ``` select u.id, u.fullname, r.* from users as u right outer join rapports as r on u.id = r.userid where u.active = 1 and (r.closed = 0 or CONVERT(varchar, r.periodstart, 112) = convert(varchar, GETDATE(), 112)) order by u.fullname ``` But this only shows records from `user-table` if there is a record in `rapports-table` matching the `WHERE`-conditions. Is it anyhow possible?

Original source