Firstly - many thanks Martin and anyone else that responds.

--- In firebird-support@yahoogroups.com, "Martijn Tonies"
<m.tonies@u...> wrote:

> Care to tell us an example query, metadata involved and the query
> plan?

Certainly, the example given previously and outlined below is the
simplest possible case to consider.

1. Example of a Master table: CONT (Contractor)
CREATE TABLE CONT
(
CONTID INTEGER NOT NULL,
SURNAME VARCHAR(20) NOT NULL,
EMAIL VARCHAR(50),
... more
)

2. Example of a Child table: CONTSKILL (Contractor Skill)
CREATE TABLE CONTSKILL
(
SKILLID INTEGER NOT NULL,
CONTID INTEGER NOT NULL,
RATING SMALLINT NOT NULL,
CONSTRAINT PK_CONTSKILL PRIMARY KEY (SKILLID, CONTID)
);

... INDEX IDXCONTSKILLRATING ON CONTSKILL(RATING);
.. FK_CONTSKILL1 FOREIGN KEY (SKILLID) REFERENCES SKILL (ID);
.. FK_CONTSKILL2 FOREIGN KEY (CONTID) REFERENCES CONT (CONTID);

3. Master Skill Table
CREATE TABLE SKILL
(
ID INTEGER NOT NULL,
SKILLCATID INTEGER NOT NULL,
SKILL VARCHAR(30) NOT NULL,
DEFINITION VARCHAR(255) NOT NULL,
CONSTRAINT PK_SKILL PRIMARY KEY (ID)
);
... FK_SKILL FOREIGN KEY (SKILLCATID) REFERENCES SKILLCAT (ID);

> > - Child table uses heavy GROUP BY processing to ensure matches.
>
> What does this mean?

If 5 skills are selected (3 of which are required, 2 optional) then it
loads the following table (see example later below):

CREATE TABLE QRYDEFSKILL
(
USERID INTEGER NOT NULL,
SKILLID INTEGER NOT NULL,
NEEDED INTEGER NOT NULL,
RATING INTEGER,
CONSTRAINT PK_QRYDEFSKILL PRIMARY KEY (USERID, SKILLID)
);

and then hands to a SP that performs a JOIN between CONTSKILL &
QRYDEFSKILL GROUPING BY cs.contid HAVING COUNT(*)=3 on the 3 required
values and in the FOR...SELECT performs a EXISTS() for any of the
remaining optional values. Records found are inserted into QRYTMPx.

The sp is called as part of the following steps:
1. DELETE FROM QRYTMPx
2. DELETE FROM Qrydefskill WHERE userid=3
3. INSERT INTO Qrydefskill (userid, skillid, rating, needed )
VALUES (3, 2393, 9999, 1)
4. INSERT INTO QRYTMPx SELECT id FROM spQryCandidate(3)

5. SELECT c.contid, c.surname, c.forename, c.dob, c.postcode, c.filed,
c.available, c.email FROM QRYTMPx q, cont c WHERE q.id=c.contid


CREATE TABLE QRYTMPx
(
ID INTEGER NOT NULL,
PRIMARY KEY (ID)
);

Note: point 5 fairly often has other criteria attached to cont table.

Tom