--- In firebird-support@yahoogroups.com, Didier Gasser Morlay wrote:

> 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)
> )
>
> I must be missing the obvious and any idea will be very much
> appreciated

Well, Didier, if it is what I suspect, then it is not obvious for the
person that wrote the query:

I think that the subselect takes both clearance and cl to refer to the
subselect table, hence there will never be a not exists whenever there
is any record in clearance with a non-null employee_id. Even though
you create an alias for the subselect table, that does not mean that
InterBase believes clearance.employee_id to refer to the main table (I
wrote InterBase because I would be somewhat surprised if Firebird
didn't object to the syntax - even though I haven't checked). Change
your query to

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

and it would (hopefully) give the result you want.

Set