Hi Tom!

An alternative to

SELECT cs.contid, count(cs.skillid)
FROM contskill cs
join QRYDEFSKILL qds on cs.skillid = qds.skillid
GROUP BY cs.contid HAVING COUNT(*)=3

is to use

SELECT cs.contid
FROM contskill cs
WHERE NOT EXISTS(
SELECT * FROM QRYDEFSKILL qds
WHERE qds.needed = 1
AND NOT EXISTS(
SELECT * FROM contskill cs2
WHERE cs2.skillid = qds.skillid
AND cs.contid = cs2.contid))

(i.e. find the persons for which there is no required skill that they
don't have)

Though I must admit I do not know how a nested NOT EXISTS performs
compared to a GROUP BY with HAVING (I just know that single [NOT]
EXISTS are normally fast).

HTH,
Set

--- In firebird-support@yahoogroups.com, "tomconlon7777777" wrote:
> Firstly - many thanks Martin and anyone else that responds.
>
> --- In firebird-support@yahoogroups.com, "Martijn Tonies" 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