Hi,

We have two tables:

T1 - which holds the 'objects' with the following structure
DOCSID - PK
SECID - PK (we need two fields here for the PK...)
NAME - VARCHAR(...
...<aso> - many other fields here.

T2 - which holds the 'properties' of the above objects (think at it as
an 'Object Inspector'):
ID - Abstract PK
DOCSID - FK in T1
SECID - FK in T1
PROP - The name of the Property (eg. 'Name', 'Color', 'Tag',
'Height', 'Width', 'Top' aso.)
VAL - The value of the Property (eg. 'edtTest', clRed, 0, 30, 50,
12 aso.)
GROUPNO - integer - this groups the pairs to one sub-object. The
'objects' from T1 can be, in fact, 'containers' which holds degenerated
sub-objects. For ex.:

T2:
ID DOCSID SECID PROP VAL GROUPNO
1 888 2 FName John 1
2 888 2 LName Doe 1
3 888 2 Role Worker 1
4 888 2 FName Jane 2
5 888 2 LName Doe 2
6 888 2 Role Manager 2
7 888 3... <other object here>
...

We have also a memory table (let's call it M1) with the following structure:
NOT_COND - boolean
PROP - <can be also null> - Null here means doesn't matter
VAL - <can be also null> - Null here means doesn't matter

and a DBGrid linked to the memory table in which the user can fill the
desired query (something similar with QBE approach). For ex.:

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.