Hello,

I have got the following table

clearance:
employee_id integer not null,
business_line integer null,
limit integer not null.

Which describes for each employee what is his/her trading limit (authority) per business_line.

an employee may have an overall limit of 1000 for any business (the business_line will then be null) but be considered as a junior for certain
business_line and have a smaller limit while he/she is in training. (the contrary migh be true also, he is junior excpet for a busuness_line where he
is fully trained and has a higher limit)

I would then have the following records

employee_id business_line Limit
1 <null> 1000
2 1 500
3 2 500
4 <null> 1000
4 1 50
I am trying to get for a business_line (for example 1) the list of all employees along with their limit.

the result set I am after would be

employee 1 : limit 50
employee 2 : limit 500
employee 4 : limit 1000

the problem, in case of employee 1 is to recognize that he has a more precise limit

This is the request I wrote

Select employee_id, limit
from clearance
where business_line_id = 1 or
(
business_line_id is null
and not exists
(select * from clearance cl where cl.employee_id = clearance.employee_id and cl.business_line_id = 1)
)

This query returns
Employee 2 limit 500
Employee 4 limit 50

but does not return employee 1 and I fail to see why.

I must be missing the obvious and any idea will be very much appreciated

Thanks in advance

Didier