> NOT_COND PROP VAL
> [ ] Age 25
> [ ] FirstName John
> [ ] LastName Doe
> [X] Married <empty>
> [ ] -Unknown-
>
> The above query means that we want all the objects with have the name
> 'John Doe' age of 25, we don't know their marital status (ie. the
> 'Married' property ISN'T present) and have at least one property which
> have the string '-Unknown-' as it's value.
>
> (In fact, the tables are a bit more complicated, but I present here a
> simplified case - for ex. Prop is an integer which points to another
> lookup table, T2 has more fields... etc).
>
> How can we build a general query to return the objects from T1 which
> have the desired condition(s) once (and only once) and to cover all the
> cases?
>
> Our (planned) approach: (note that we skipped the 'Not' married case - I
> cannot imagine now how to handle it...)
>
> Select * from t1
> where exists(
> Select t2.docsid from t2 where
> (
> (t2.prop='Age' and t2.val='25') or
> (t2.prop='FirstName' and t2.val='John') or
> (t2.prop='LastName' and t2.val='Doe') or
> (t2.val='-Unknown-')
> ) and (t1.docsid=t2.docsid) and (t1.secid=t2.secid) /* the link to
> the above t1 */
> group by t2.docsid, t2.secid, t2.groupno /*return only
> once*/
> having count(*)=4 /* 4 = RecCount of M1 - if we skip the
> "[X] Married <empty>" record
> );
>
> Can someone comment/improve this? It is ok? How to handle the NOT_COND case?
>
> TIA,
>
> m. Th.

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.

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, whereas if he was named John Michael Doe he should be included? I won't propose any solution to any such potential problem until you enlighten my understanding.

HTH,
Set


[Non-text portions of this message have been removed]