Arbaaz Arbaaz - 2 months ago 7
SQL Question

How to display null if join condition is not met

I have a table

tbl_expenseLimit
which I use to get the
Limit
for a given combination of variables.

tbl_expenselimit

+------+---------+----------+---------+-------+----------+
| SrNo | ExpType | Location | Expense | Limit | empgrade |
+------+---------+----------+---------+-------+----------+
| 1 | ERA | Metro | 1 | 250 | A |
| 2 | ERA | Metro | 2 | 500 | A |
| 3 | ERA | Metro | 3 | 5000 | A |
+------+---------+----------+---------+-------+----------+


If
ExpType
in my main table is ERA,
Location
is Metro,
Expense
is 1 and
empgrade
is 'A' then
Limit
would be 250.

If any of the condition does not match, for example: If
ExpType
is ERA,
Expense
is 1 ,
empgrade
is 'A' but
Location
is Non-Metro then
Limit
should be
NULL
. Similarly if all conditions match but empgrade is 'B' (which is not present in
tbl_expenselimit
table it should return
NULL
for
Limit


select other.srno,VoucherId,other.ExpType, lmt.Limit
from tbl_voucherotherexpense other
left join tbl_TypesOfExpenses expensemst
on other.expense=expensemst.srno
left join tbl_expenseLimit lmt
on (other.Expense=lmt.Expense
and other.ExpType=lmt.ExpType
and (( lmt.location is not null
and other.location=lmt.location) or 1=1))
where voucherid='C0000004' and lmt.empgrade='a'


The join between
tbl_voucherotherexpense
and
tbl_TypesOfExpenses
will always find a match. So its just
tbl_expenseLimit
that needs to be focused on.

The query that I have written is wrong. It does not achieve what I want. Any help is appreciated.

EDIT:

Suppose in my main table
ExpType
is ERA,
Location
is Metro,
Expense
is 2 but
empgrade
is B then output should be :

+------+-----------+---------+-------+
| srno | VoucherId | ExpType | Limit |
+------+-----------+---------+-------+
| 4 | C0000004 | ERA | NULL |
+------+-----------+---------+-------+


or if
in my main table
ExpType
is ERA,
Location
is Metro,
empgrade
is A but
Expense
is 12 then output should still be the same as above.

Answer

All the conditions that refer to the table you're left joining i.e. lmt should go in the on, which, unlike the where clause, won't remove rows where the condition isn't matched:

select other.srno,VoucherId,other.ExpType, lmt.Limit 
from tbl_voucherotherexpense other left join tbl_TypesOfExpenses expensemst on other.expense=expensemst.srno 
left join tbl_expenseLimit lmt 
    on other.Expense=lmt.Expense 
    and other.ExpType=lmt.ExpType 
    and other.location=lmt.location   
    and lmt.empgrade='a' 
where voucherid='C0000004'