> Possibilities:
> 1. Clever use of GROUP BY?
> 2. Loading a small table then using a sp for 111,222,333
> sp 'cursor' to check 444,555
> 3. UNION ALL seems to work but UNION is limited in FB
> 4. Extract 111,222,333 then use client-side to process? (IBO)

Hi again,

Method 2 yielded excellent results, including the ability to apply
ratings - both in the mandatory skills plus the optional ones.

Final need is to link the results to get more data from the main
table (cont) which may include applying other criteria to that select
(ie postalcode match, etc).

Standard join syntax is too slow yet I did not want to include the
main table in the sp unless the user wanted to use the results. Any
ideas anyone? Standard join/sub-select possible or not?

Tom

(ps. For anyone interested the sp is below. The person table in the
example was actually 'cont' (for contractor)).

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

CREATE TABLE CONTSKILL
(
SKILLID INTEGER NOT NULL,
CONTID INTEGER NOT NULL,
RATING SMALLINT default 0 NOT NULL,
CONSTRAINT PK_CONTSKILL PRIMARY KEY (SKILLID, CONTID)
);


CREATE PROCEDURE SPGETCONTSKILL ( PUSERID INTEGER)
RETURNS( ID INTEGER)
AS
DECLARE VARIABLE icount INTEGER;
DECLARE VARIABLE ineeded INTEGER;
DECLARE VARIABLE isuspend INTEGER;
BEGIN
SELECT COUNT(*), SUM(needed) FROM QryDefSkill WHERE
userid=:puserid INTO icount, ineeded;
IF (:ineeded > 1) THEN
BEGIN
FOR
SELECT ContSkill.contid FROM ContSkill
INNER JOIN QryDefSkill ON
(ContSkill.skillid=QryDefSkill.skillid) AND
(ContSkill.rating>=QryDefSkill.rating)
WHERE QryDefSkill.userid=:puserid AND QryDefSkill.needed>0
GROUP BY ContSkill.contid HAVING COUNT(*) = :ineeded INTO id
DO
BEGIN
IF (:icount=:ineeded) THEN
BEGIN
SUSPEND;
END
ELSE IF (EXISTS(SELECT ContSkill.contid FROM ContSkill
INNER JOIN QryDefSkill ON
(ContSkill.skillid=QryDefSkill.skillid) AND
(ContSkill.rating>=QryDefSkill.rating)
WHERE QryDefSkill.userid=:puserid AND
QryDefSkill.needed=0 AND
ContSkill.contid=:id)) THEN
BEGIN
SUSPEND;
END
END
END
ELSE
BEGIN
FOR
SELECT ContSkill.contid FROM ContSkill
INNER JOIN QryDefSkill ON
(ContSkill.skillid=QryDefSkill.skillid) AND
(ContSkill.rating>=QryDefSkill.rating)
WHERE QryDefSkill.userid=:puserid INTO id
DO
SUSPEND;
END
END
---------------------------------------