Svein Erling Tysvær wrote:

>> [...]
>>
>
> Normally, I'd prefer something like:
>
> select t1.*
> from Table1 t1
> join Table2 t2a on t1.docsid=t2a.docsid and t1.secid=t2a.secid
> join Table2 t2b on t1.docsid=t2b.docsid and t1.secid=t2b.secid and t2a.groupno = t2b.groupno
> join Table2 t2c on t1.docsid=t2c.docsid and t1.secid=t2c.secid and t2a.groupno = t2c.groupno
> join Table2 t2d on t1.docsid=t2d.docsid and t1.secid=t2d.secid and t2a.groupno = t2d.groupno
> left join Table2 t2e on t1.docsid=t2e.docsid and t1.secid=t2e.secid and t2a.groupno = t2e.groupno
> and t2e.prop='Married'
> where t2a.prop='Age' and t2a.val='25'
> and t2b.prop='FirstName' and t2b.val='John'
> and t2c.prop='LastName' and t2c.val='Doe'
> and t2d.val='-Unknown-'
> and t2e.docsid is null
>
> (alternatively, it is possible to use a NOT EXISTS if you think the LEFT JOIN part is harder to understand)
>
> A possible benefit of doing it this way as opposed to your original solution, is that indexes may be used to a greater extent (the optimizer may not have to go NATURAL on Table1), hence it is more likely to work satisfactorily with larger tables.
>
>

Thanks Svein for your quick response! Good point about the indexes. But
if the number of conditions grows (let's say that our memory table
holding the QBE definition can have till 15 rows) then do you think that
your solution, which implies a join with 15 tables, will be faster?

> I didn't understand what you meant by "have the desired condition(s) once (and only once)". Do you mean that if a person is named "John John Doe" and have these three records in t2:
>
> 1 888 2 FName John 1
> 2 888 2 FName John 1
> 3 888 2 LName Doe 1
>
> then he should be excluded from the result,

No. In your above example, if we tell to DB to fetch all the 'objects'
which have the 'FName=John' to not bring the 888.2 twice.

> <...> if he was named John Michael Doe he should be included?

Yes.

> I won't propose any solution to any such potential problem until you enlighten my understanding.
>
> HTH,
> Set
>

TIA,


m. th.