Left join not pulling through nulls where expected.

Question

I have a query that looks something like this (I've changed the table names):

select @user_id
,      isnull(ur.rule_value, isnull(manr.rule_value, def.rule_value)) [rule_value]
,      isnull(urt.name, isnull(manrt.name, def.name)) [rule_type]
from   (select @user_id [user_id]
        , rule.rule_value
        , rule_type.name
        from rule
        join rule_type on rule_type.rule_type_id = rule.rule_type_id
        where rule.user_id = 1) def
join   user on user.user_id = def.user_id
join   manager man on man.manager_id = user.manager_id
left join rule ur on ur.user_id = user.user_id
left join rule_type urt on urt.rule_type_id = ur.rule_type_id
left join rule manr on manr.manager_id = man.manager_id
left join rule_type manrt on manrt.rule_type_id = manr.rule_type_id

What I'm expecting is that when there isn't a rule for the user or the user's manager, then the default rule should be used. However, I'm only getting a result if the user has a rule.

I've tried left joining everything but to no avail, and the select statement for the def table brings back all the default rules.

What am I doing wrong?

@user_id is a variable.

Update

Example of schema

rule
rule_id user_id manager_id rule_value
1    1  1   27
2    1  1   24
3    1  1   25
4    1  1   44
5    1  1   88
1    2  4   2
2    2  4   23
3    2  4   18
3    NULL 4   19
4    NULL 4   20
5    NULL 4   21


rule_type
rule_id name
1    'Craziness'
2    'Number of legs'
3    'Hair ranking'
4    'Banana preference'
5    'Rule 5'

user
user_id manager_id ... other columns
1    1
2    4
3    4

manager
manager_id ... other columns
1
2
3
4
5
6

So if @user_id is 2 then I would expect the output

2, 2, 'Craziness'
2, 23, 'Number of legs'
2, 18, 'Hair ranking'
2, 20, 'Banana preference'
2, 21, 'Rule 5'

But if @user_id is 3 then I would expect the output

3, 27, 'Craziness'
3, 24, 'Number of legs'
3, 19, 'Hair ranking'
3, 20, 'Banana preference'
3, 21, 'Rule 5'

Answer 1

A regular join is actually a left inner join. What you actually want is an outer join, which fetches the result, even if it cannot join it with the other table.

Answer 2

To answer the question in the title, a LEFT JOIN does NOT act like a REGULAR JOIN. The LEFT JOIN is the same as the LEFT OUTER JOIN, meaning will also return the records in the LEFT table that don't match the ON criteria.

Answer 3

Get rid of this :

left join rule ur on ur.user_id = user.user_id
left join rule_type urt on urt.rule_type_id = ur.rule_type_id
left join rule manr on manr.manager_id = man.manager_id
left join rule_type manrt on manrt.rule_type_id = manr.rule_type_id

and replace it with this :

left join rule ur on ur.user_id = user.user_id or ur.user_id = man.manager_id
left join rule_type urt on urt.rule_type_id = ur.rule_type_id

then change your opening SELECT to :

select @user_id
,      isnull(ur.rule_value, def.rule_value) [rule_value]
,      isnull(urt.name, def.name) [rule_type]

Essentially what you're doing wrong is that you have one entity (user-manager-default) and you're trying to link it to a different entity (rule) via two different joins. If one join doesn't work but the other does, there's no matching NULL returned for your ISNULL to find. It's a little counter-intuitive.

Answer 4

I've split the query into two parts.

I think because the table structures are more complicated than I put into the original question, this means that CodeByMoonlight's answer isn't quite enough - it was giving me duplicate rows that I couldn't account for.

My solution is as follows (with respect to the details I gave in the question):

create table #user_rules
(
 user_id int,
 rule_value int,
 rule_type varchar(255),
 rule_type_id,
)
--Insert default values
insert into #user_rules
select @user_id [user_id]
    , rule.rule_value
    , rule_type.name
    , rule_type.rule_type_id
from rule
join rule_type on rule_type.rule_type_id = rule.rule_type_id
where rule.user_id = 1
--Update table with any available values
update #user_rules
set rule_value = ur.rule_value
from user u
join manager m on u.manager_id = m.manager_id
join rule ur on ur.user_id = u.user_id or (ur.manager_id = man.manager_id and ur.user_id is null)
join rule_type urt on urt.rule_type_id = ur.rule_type_id
where urt.rule_type_id = #urse_rules.rule_type_id
and u.user_id = @user_id