Try reposting it with DDL and sample data!!!!!!!!!!!1

--
Dean Savovic
www.teched.hr


[quoted text, click to view]

You can translate Boolean expressions into pure SQL for elaborate
searches without using dynamic SQL. For example, given a table of job
candidates and their skills, you might write a query like this:

skill = Java AND (skill = Perl OR skill = PHP)

now you need to put it in the disjunctive canonical form. In English,
for the non-computer science majors, that mean a string of AND-ed groups
connected by OR operators like this:

(Java AND Perl) OR (Java AND PHP)

which we load into this table:

CREATE TABLE SkillQuery
(and_grp INTEGER NOT NULL,
skill CHAR(4) NOT NULL,
PRIMARY KEY (and_grp, skill));

INSERT INTO SkillQuery VALUES (1, 'Java');
INSERT INTO SkillQuery VALUES (1, 'Perl');
INSERT INTO SkillQuery VALUES (2, 'Java');
INSERT INTO SkillQuery VALUES (2, 'PHP');

Assume we have a table of job candidates:

CREATE TABLE Candidates
(candidate_name CHAR(10) NOT NULL,
skill CHAR(4) NOT NULL,
PRIMARY KEY (candidate_name, skill));

INSERT INTO Candidates VALUES ('John', 'Java'); --winner
INSERT INTO Candidates VALUES ('John', 'Perl');
INSERT INTO Candidates VALUES ('Mary', 'Java'); --winner
INSERT INTO Candidates VALUES ('Mary', 'PHP');
INSERT INTO Candidates VALUES ('Larry', 'Perl'); --winner
INSERT INTO Candidates VALUES ('Larry', 'PHP');
INSERT INTO Candidates VALUES ('Moe', 'Perl'); --winner
INSERT INTO Candidates VALUES ('Moe', 'PHP');
INSERT INTO Candidates VALUES ('Moe', 'Java');
INSERT INTO Candidates VALUES ('Celko', 'Java'); -- loser
INSERT INTO Candidates VALUES ('Celko', 'Algol');
INSERT INTO Candidates VALUES ('Smith', 'APL'); -- loser
INSERT INTO Candidates VALUES ('Smith', 'Algol');

The query is simple now:

SELECT DISTINCT C1.candidate_name
FROM Candidates AS C1, SkillQuery AS Q1
WHERE C1.skill = Q1.skill
GROUP BY Q1.and_grp, C1.candidate_name
HAVING COUNT(C1.skill)
= (SELECT COUNT(*) FROM SkillQuery AS Q2 WHERE Q1.and_grp =
Q2.and_grp);

You can retain the COUNT() information to rank candidates. For example
Moe meets both qualifications, while other candidates meet only one of
the two.

This query can be generalized for any number of columns. A UNION on
queries for each of the possible columns will be faster than putting it
all into a monzster query.

--CELKO--
===========================
Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
schema are.

*** Sent via Developersdex http://www.developersdex.com ***

Here is a slight round-about solution.
SELECT DISTINCT EmployeeNo FROM testTable WHERE
EmployeeNo IN (SELECT EmployeeNo FROM testTable WHERE (SkillValue_ID = 1
OR SkillValue_ID = 10 OR SkillValue_ID = 6)) AND
EmployeeNo IN (SELECT EmployeeNo FROM testTable WHERE (SkillValue_ID = 2
OR SkillValue_ID = 9)) AND
EmployeeNo IN (SELECT EmployeeNo FROM testTable WHERE (SkillValue_ID = 3
OR SkillValue_ID = 8 OR SkillValue_ID = 13))
--
HTH,
SriSamp
Please reply to the whole group only!
http://www32.brinkster.com/srisamp

[quoted text, click to view]

Your query will not work. Check it out in Query Analyzer. No rows will be
returned.
--
HTH,
SriSamp
Please reply to the whole group only!
http://www32.brinkster.com/srisamp

[quoted text, click to view]