Set,

THANK YOU, you're right.

I should have remembered that in a situation like this, both tables need aliasing. (can't remember in which release notes the aliasing was mentioned,
but there was definitely something)

Thanks for taking the time,

Didier


Subject: Re: Subselect help --- 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