Better way of detecting the presence of a criteria but absence of one which meets requirements?

Question

Post reply

Better way of detecting the presence of a criteria but absence of one which meets requirements?

kramaswamy

SSCoach

Points: 18181
More actions
October 24, 2014 at 11:57 am

#294562

Subject is probably not the most clear, so I'll try to explain better.
Basically, I have a table which stores criteria for a campaign. I have another table which has potential candidates for campaigns.
What I want to do is find all candidates which do not meet a specific criteria for a campaign - however, I want to write this query in a way that will not exclude candidates for a campaign which does not have this criteria requirement.
Normally, I would do something like this :
UPDATE a
SET a.Status = 'INVALID'
FROM CandidateTable a
LEFT JOIN CriteriaTable b ON b.CampaignID = a.CampaignID AND b.Criteria = a.Criteria AND b.CriteriaTypeID = 1
WHERE b.Criteria IS NULL
Problem with this query is that, if the campaign in question doesn't have any requirements for criteria type 1, this query will just flag all the records as being invalid.
The version of the query I'm running with right now is the following :
UPDATE a
SET a.Status = 'INVALID'
FROM CandidateTable a
WHERE EXISTS (SELECT 1 FROM CriteriaTable b WHERE b.CampaignID = a.CampaignID AND b.CriteriaTypeID = 1)
AND NOT EXISTS (SELECT 1 FROM CriteriaTable b WHERE b.CampaignID = a.CampaignID AND b.CriteriaTypeID = 1 AND b.Criteria = a.Criteria)
It's a bit brute-force and ugly though. Is there a cleaner way to do it?
Here's the TSQL:
CREATE TABLE #CandidateTable
(
ID INT IDENTITY PRIMARY KEY,
CandidateID INT,
Campaign INT,
Criteria VARCHAR(10),
[Status] VARCHAR(20)
)
CREATE TABLE #CriteriaTable
(
ID INT IDENTITY PRIMARY KEY,
CriteriaTypeID INT,
Campaign INT,
Criteria VARCHAR(10)
)
INSERT INTO #CandidateTable (CandidateID, Campaign, Criteria)
VALUES (1, 1, 'ABC')
INSERT INTO #CandidateTable (CandidateID, Campaign, Criteria)
VALUES (1, 2, 'AAA')
INSERT INTO #CandidateTable (CandidateID, Campaign, Criteria)
VALUES (1, 3, 'ASD')
INSERT INTO #CandidateTable (CandidateID, Campaign, Criteria)
VALUES (2, 1, '111')
INSERT INTO #CandidateTable (CandidateID, Campaign, Criteria)
VALUES (2, 2, '333')
INSERT INTO #CandidateTable (CandidateID, Campaign, Criteria)
VALUES (2, 3, 'ASD')
INSERT INTO #CandidateTable (CandidateID, Campaign, Criteria)
VALUES (3, 1, 'ABC')
INSERT INTO #CandidateTable (CandidateID, Campaign, Criteria)
VALUES (3, 2, '333')
INSERT INTO #CandidateTable (CandidateID, Campaign, Criteria)
VALUES (3, 3, 'ASD')
INSERT INTO #CriteriaTable (CriteriaTypeID, Campaign, Criteria)
VALUES (1, 1, 'ABC')
INSERT INTO #CriteriaTable (CriteriaTypeID, Campaign, Criteria)
VALUES (2, 1, 'AAA')
INSERT INTO #CriteriaTable (CriteriaTypeID, Campaign, Criteria)
VALUES (1, 2, 'AAA')
INSERT INTO #CriteriaTable (CriteriaTypeID, Campaign, Criteria)
VALUES (2, 2, 'ABC')
INSERT INTO #CriteriaTable (CriteriaTypeID, Campaign, Criteria)
VALUES (2, 3, '333')
INSERT INTO #CriteriaTable (CriteriaTypeID, Campaign, Criteria)
VALUES (3, 3, 'ASD')
--UPDATE a
--SET a.Status = 'INVALID'
--FROM #CandidateTable a
--LEFT JOIN #CriteriaTable b ON b.Campaign = a.Campaign AND b.Criteria = a.Criteria AND b.CriteriaTypeID = 1
--WHERE b.Criteria IS NULL
UPDATE a
SET a.Status = 'INVALID'
FROM #CandidateTable a
WHERE EXISTS (SELECT 1 FROM #CriteriaTable b WHERE b.Campaign = a.Campaign AND b.CriteriaTypeID = 1)
AND NOT EXISTS (SELECT 1 FROM #CriteriaTable b WHERE b.Campaign = a.Campaign AND b.CriteriaTypeID = 1 AND b.Criteria = a.Criteria)
SELECT * FROM #CandidateTable
SELECT * FROM #CriteriaTable WHERE CriteriaTypeID = 1
DROP TABLE #CandidateTable
DROP TABLE #CriteriaTable

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

g.britton SSChampion Points: 13863 More actions · Answer 1

why not:

UPDATE a

SET a.Status = 'INVALID'

FROM CandidateTable a

JOIN CriteriaTable b

ON b.CampaignID = a.CampaignID AND b.CriteriaTypeID = 1 -- same as the WHERE EXISTS clause

WHERE NOT EXISTS (SELECT 1 FROM CriteriaTable b WHERE b.CampaignID = a.CampaignID AND b.CriteriaTypeID = 1 AND b.Criteria = a.Criteria)

Gerald Britton, Pluralsight courses

kramaswamy SSCoach Points: 18181 More actions · Answer 2

Sure, although really that's the same solution - doing an EXISTS or a JOIN will be roughly equivalent in performance.

What I was hoping for, was a version of this query which wouldn't require me to do two queries of the same table, and instead have it be done in one shot like the version which doesn't work.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 3

Is Campaign 3 not invalid since it does not exist in the CriteriaTable?

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 4

Edit: Scratch my solution. Looks like not all the criteria are loading into the temp table.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 5

After rechecking my sandbox, it looks like my code my still be valid, so here it is again.

CREATE TABLE #CandidateTable

(

ID INT IDENTITY PRIMARY KEY,

CandidateID INT,

Campaign INT,

Criteria VARCHAR(10),

[Status] VARCHAR(20)

)

CREATE TABLE #CriteriaTable

(

ID INT IDENTITY PRIMARY KEY,

CriteriaTypeID INT,

Campaign INT,

Criteria VARCHAR(10)

)