Re: RE : [back-sql] Problem with NOT search requests (ITS#2652)

[ando@sys-net.it]

> It seems to me that the result of the second query is included in the
> result of the first query.
> All persons who do not have a given phone number include persons who do
> not have a phone number at all.
> Don't you think so ?

>From the fist query you get people that indeed have a phone number,
because you're joining the "phones" table; this is a common mistake I ran
into many times.  That's why you need two queries: one to select those
that have a phone number that doesn't match, and one for those that don't.
 In the (black magic to me) variety of join mechs there might be an option
that allows to perform all in one (outer join?).

p.

>
> -----Message d'origine-----
> De : Pierangelo Masarati [mailto:openldap-its@OpenLDAP.org]
> Envoyé : dimanche 18 avril 2004 22:45
> À : emmanuel.duru@atosorigin.com
> Objet : Re: [back-sql] Problem with NOT search requests (ITS#2652)
>
>
>> >
>>>I don't think this problem can be easily overcome.
>>>I can easily figure out a simple query that does this
>>>if the filter is of the type (!<ava>); if the filter
>>>is (!<complex filter>) things get much more involved.
>>>We might have to live with it.
>>>
>>>p.
>>
>> Did you consider nested queries ?
>> For example, for the search filter (!telephonenumber=<a value>),
>
> My mistake: the filter is rather (!(telephonenumber=<a value>)),
>
>> we would get candidates with the following query :
>> 'select distinct persons.id from persons where not persons.id
>>       in (select persons.id from persons, phones where
>>           phones.phone='<a value>' and persons.id=phones.id)'
>> This may be a generic way to process not filters, though this may
>> generate complex SQL queries.
>
> This is not likely to work, because a filter of the form
>
>         (!(telephonenumber=<a value>))
>
> should match not only rows resulting from
>
>         select
>                 distinct persons.id
>         from
>                 persons
>         where
>                 persons.id not in (select
>                         persons.id
>                 from
>                         persons,phones
>                 where
>                         phones.phone='<a value>'
>                         and persons.id=phones.id)
>
> but also rows resulting from
>
>         select
>                 distinct
>         persons.id
>                 from persons
>         where
>                 persons.id not in (select
>                         phones.i
>                 from
>                         phones)
>
> which could be obtained by
>
>         select
>                 distinct persons.id
>         from
>                 persons
>         where
>                 persons.id not in (select
>                         persons.id
>                 from
>                         persons,phones
>                 where
>                         phones.phone='<a value>'
>                         and persons.id=phones.id)
>                 and persons.id not in (select
>                         phones.id
>                 from
>                         phones)
>
> This would make things quite cumbersome for more involved queries. Maybe
> it's worth trying; I'll have a look at this later.
>
> p.


-- 
Pierangelo Masarati
mailto:pierangelo.masarati@sys-net.it