LEFT OUTER JOIN on multiple conditions Is there a way to link two tables in order?

Table A (Actual):

EX  STATUS  DLV     Actual FEE    Expected Fee
N    ACT    MAIL    1.25
N    ACT    EMAIL   1.25
N    ACT    PICKUP  0
N    NON    MAIL    0
N    NON    EMAIL   1.25
N    NON    PICKUP  0
Y                   0


Table B (Expected):

EX  STATUS  DLV     Expected FEE
N    ACT    MAIL    1.25
N    ACT    EMAIL   0
N    ACT    PICKUP  0
N    NON    MAIL    1.25
N    NON    EMAIL   0
N    NON    PICKUP  0
Y                   0


Table A (Expected Result):

EX  STATUS  DLV     Actual FEE    Expected Fee
N    ACT    MAIL    1.25          1.25
N    ACT    EMAIL   1.25          0
N    ACT    PICKUP  0             0
N    NON    MAIL    0             1.25
N    NON    EMAIL   1.25          0
N    NON    PICKUP  0             0
Y                   0             0


I want to incorporate the follow logic within the join:
A.EX = B.EX (join on first)

but then on the combination of the other 3, since they are unique.  

I tried this but of course it didn't work:

FROM
dbo.claims a
left outer join dbo.pricing p on a.EX = p.EX 
and a.STATUS = p.STATUS
and a.DLV = p.DLV 


I was hoping to link table B to table A to get the expected fee.
If Ex = Y, then it's 0 regardless of status or DLV.  If Ex=N, then I have to depend on the other two columns.

I'm sorry if this is confusing.
create table tableA (EX char(1), STATUS char(3), DLV varchar(10), Actual_FEE decimal(18,2));
insert into tableA values
('N', 'ACT', 'MAIL',   1.25),
('N', 'ACT', 'EMAIL',  1.25),
('N', 'ACT', 'PICKUP', 0),
('N', 'NON', 'MAIL',   0),
('N', 'NON', 'EMAIL',  1.25),
('N', 'NON', 'PICKUP', 0),
('Y', null,  null,     0);
GO



7 rows affected






create table tableB (EX char(1), STATUS char(3), DLV varchar(10), Expected_FEE decimal(18,2));
insert into tableB values
('N', 'ACT', 'MAIL',   1.25),
('N', 'ACT', 'EMAIL',  0),
('N', 'ACT', 'PICKUP', 0),
('N', 'NON', 'MAIL',   1.25),
('N', 'NON', 'EMAIL',  0),
('N', 'NON', 'PICKUP', 0),
('Y', null,  null,     0);
GO



7 rows affected



According to your expected result it could be:




select    a.EX, a.STATUS, a.DLV, a.Actual_FEE, b.Expected_FEE
from      tableA a
left join (select distinct EX, STATUS, DLV, Expected_Fee
           from   tableB) b
on        a.EX = b.EX
and       (a.STATUS = b.STATUS OR b.STATUS IS NULL)
and       (a.DLV = b.DLV or b.DLV IS NULL)
GO



EX | STATUS | DLV    | Actual_FEE | Expected_FEE
:- | :----- | :----- | :--------- | :-----------
N  | ACT    | MAIL   | 1.25       | 1.25        
N  | ACT    | EMAIL  | 1.25       | 0.00        
N  | ACT    | PICKUP | 0.00       | 0.00        
N  | NON    | MAIL   | 0.00       | 1.25        
N  | NON    | EMAIL  | 1.25       | 0.00        
N  | NON    | PICKUP | 0.00       | 0.00        
Y  | null   | null   | 0.00       | 0.00        



dbfiddle here