SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


EXISTS/ ALL?


EXISTS/ ALL?

Author
Message
pietlinden
pietlinden
SSC-Forever
SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)

Group: General Forum Members
Points: 42736 Visits: 16085
Is there a way in SSRS to convert a delimited list to a series of "AND EXISTS()" subqueries? In SSRS, it's really easy to use a multi-valued parameter with IN() to filter the results returned. The same result can be obtained using DelimitedSplit8K and IN(). But is it possible to OR the results instead without resorting to dynamic SQL? Here's a quick example (yes, I know the Candidate table isn't terribly realistic!)

CREATE TABLE Candidate(
CandidateID INT IDENTITY,
FirstName VARCHAR(15) NOT NULL
CONSTRAINT pkCandidate PRIMARY KEY (CandidateID),
CONSTRAINT uqFirstName UNIQUE (FirstName));

CREATE TABLE CandidateSkills(
SCandidateID INT,
Skill VARCHAR(10)
CONSTRAINT pkCS PRIMARY KEY (ScandidateID, Skill),
CONSTRAINT fkCandidate FOREIGN KEY (SCandidateID) REFERENCES Candidate(CandidateID));

INSERT INTO Candidate(FirstName) VALUES ('John'),('Jane'),('Robert');

INSERT INTO CandidateSkills(SCandidateID,Skill)
VALUES (1,'.Net'),(1,'C#'),(2,'C#'),(3,'BA');


I can get all Candidates with both @Skill1 and @Skill2, but ... what if I have an undetermined number of skills? (use a table-valued parameter?)
but how would I modify this to use EXISTS, because I want to AND the values together, and not OR them (which is what IN() does). Would I have to pass a delimited string, split it, and then build the series of EXISTS subqueries (meaning do a lot of dynamic SQL)?

DECLARE @Skill1 VARCHAR(10) = 'C#',
@Skill2 VARCHAR(10) = '.Net';
SELECT *
FROM Candidate c
WHERE EXISTS (
SELECT 1
FROM CandidateSkills cs
WHERE cs.SCandidateID = c.CandidateID
AND cs.Skill= @Skill1 )
AND EXISTS (
SELECT 1
FROM CandidateSkills cs
WHERE cs.SCandidateID = c.CandidateID
AND cs.Skill=@Skill2 )

pietlinden
pietlinden
SSC-Forever
SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)

Group: General Forum Members
Points: 42736 Visits: 16085
Exercised my Google-fu... Relational division, anybody?
https://stackoverflow.com/questions/15977126/select-rows-that-match-all-items-in-a-list
Jason A. Long
Jason A. Long
SSChampion
SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)

Group: General Forum Members
Points: 13802 Visits: 6745
Here's an idea...

USE tempdb;
GO

IF OBJECT_ID('tempdb.dbo.Skill', 'U') IS NULL
BEGIN -- DROP TABLE tempdb.dbo.Skill;
CREATE TABLE tempdb.dbo.Skill (
skill_id INT NOT NULL IDENTITY(1,1),
skill_name VARCHAR(30) NOT NULL
CONSTRAINT pk_Skill PRIMARY KEY CLUSTERED (skill_id),
CONSTRAINT uq_Skill_skillname UNIQUE (skill_name)
);

INSERT tempdb.dbo.Skill (skill_name) VALUES ('C#'), ('.net'), ('C++'), ('T-SQL'), ('PL SQL');
END;

IF OBJECT_ID('tempdb.dbo.Candidate', 'U') IS NULL
BEGIN -- DROP TABLE tempdb.dbo.Candidate;
CREATE TABLE tempdb.dbo.Candidate (
candidate_id INT NOT NULL IDENTITY(1,1),
first_name VARCHAR(30) NOT NULL,
last_name VARCHAR(30) NOT NULL,
CONSTRAINT pk_Candidate PRIMARY KEY CLUSTERED (candidate_id)
);

INSERT tempdb.dbo.Candidate (first_name, last_name) VALUES
('Bob', 'Jones'), ('Mary', 'Baxter'), ('Kevin', 'Hall'), ('John', 'Doe'),
('Alison', 'Chains'), ('Molly', 'Hatchet'), ('Maxwell', 'House'), ('Red', 'Fox');
END;

IF OBJECT_ID('tempdb.dbo.CandidateSkills', 'U') IS NULL
BEGIN -- DROP TABLE tempdb.dbo.CandidateSkills;
CREATE TABLE tempdb.dbo.CandidateSkills (
candidate_id INT NOT NULL,
skill_id INT NOT NULL,
proficiency_level INT NOT NULL
CONSTRAINT ck_CandidateSkills_proficiencylevel CHECK (proficiency_level BETWEEN 1 AND 5),
CONSTRAINT pk_CandidateSkills PRIMARY KEY CLUSTERED (candidate_id, skill_id)
);

INSERT tempdb.dbo.CandidateSkills (candidate_id, skill_id, proficiency_level) VALUES
(1, 1, 3), (1, 2, 2), (1, 5, 4), (2, 1, 4), (2, 3, 2), (2, 4, 1),
(3, 4, 5), (3, 1, 1), (4, 5, 5), (5, 1, 2), (5, 2, 2), (5, 3, 3),
(5, 4, 1), (5, 5, 2), (6, 2, 4), (6, 4, 4), (7, 1, 3), (7, 3, 3),
(6, 5, 2), (7, 5, 5), (8, 1, 4), (8, 4, 4);
END;

--SELECT * FROM dbo.Candidate c;
--SELECT * FROM dbo.Skill s;
--SELECT * FROM dbo.CandidateSkills cs;

--=======================================================================================

DECLARE
@skill1 INT = 1,
@skill2 INT = 2;

WITH
cte_required_skills AS (
SELECT
s.skill_id
FROM
( VALUES (@skill1), (@skill2) ) s (skill_id)
),
cte_cs_eval AS (
SELECT
cs.candidate_id,
cs.skill_id,
cs.proficiency_level,
skill_count = COUNT(1) OVER (PARTITION BY cs.candidate_id),
total_proficiency = SUM(cs.proficiency_level) OVER (PARTITION BY cs.candidate_id)
FROM
cte_required_skills rs
JOIN dbo.CandidateSkills cs
ON rs.skill_id = cs.skill_id
)
SELECT
c.candidate_id,
c.first_name,
c.last_name,
s.skill_name,
ce.proficiency_level
FROM
cte_cs_eval ce
JOIN dbo.Candidate c
ON ce.candidate_id = c.candidate_id
JOIN dbo.Skill s
ON ce.skill_id = s.skill_id
WHERE
ce.skill_count = 2 --<< number of required skills.
ORDER BY
ce.total_proficiency DESC;

pietlinden
pietlinden
SSC-Forever
SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)

Group: General Forum Members
Points: 42736 Visits: 16085
Jason,
Wow. I'll take a while to get my head around all this. Say I'm using SSRS as a front end to this - Essentially "Show me all candidates that have this list of skills" In SSRS, I can make the skill list a multi-select, so how would that part work? I can get a count of items selected really easily, so I can pass a value instead of a hard-coded 2. Or is this now an SSRS question?
Thanks!
Pieter
ScottPletcher
ScottPletcher
SSC Guru
SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)

Group: General Forum Members
Points: 64914 Visits: 8725
Basically this:

SELECT SCandidateID
FROM dbo.CandidateSkills
WHERE Skill IN (@ssrs_skill_param)
GROUP BY SCandidateID
HAVING COUNT(*) = <number_of_values_in_skill_param>

But not sure how in SSRS to get a count of the values passed in. I can't remember if SSRS gives you a variable/property that equates to count or not.

Worst case, I would think you'd be able to do something like below, getting the total from the Skills "master" table:

HAVING COUNT(*) = (SELECT COUNT(*) FROM dbo.Skills WHERE Skill IN (@ssrs_skill_param))

Of course you can't use the CandidateSkills for that part, because it's possible a skill would be entered that no Candidate had, and you don't want it to drop out of the total.

SQL DBA,SQL Server MVP(07, 08, 09) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial:
If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them.
pietlinden
pietlinden
SSC-Forever
SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)

Group: General Forum Members
Points: 42736 Visits: 16085
Oh, super handy! Thanks for asking... I was playing with the SSRS end of this... Getting the count of selected values is easy in SSRS. It's the passing that back to SQL Server that could be an issue.

return list of comma-separated values: JOIN(Parameters!Invoices.Value,",")
return a count of items selected: Parameters!Invoices.Count

Just wonder if I can pass them back to the stored procedure now.. (do I need a TVP?, or can I do it an easier way?)
Luis Cazares
Luis Cazares
SSC Guru
SSC Guru (128K reputation)SSC Guru (128K reputation)SSC Guru (128K reputation)SSC Guru (128K reputation)SSC Guru (128K reputation)SSC Guru (128K reputation)SSC Guru (128K reputation)SSC Guru (128K reputation)

Group: General Forum Members
Points: 128667 Visits: 21921
Maybe this is a simpler solution:

SELECT *
FROM dbo.Candidate AS c
WHERE c.candidate_id NOT IN(
SELECT c.candidate_id
FROM dbo.Candidate AS c
CROSS JOIN dbo.Skill AS s
LEFT JOIN dbo.CandidateSkills AS cs ON c.candidate_id = cs.candidate_id AND s.skill_id = cs.skill_id
WHERE cs.candidate_id IS NULL);


It could be used with an additional condition to get specific skills

SELECT *
FROM dbo.Candidate AS c
WHERE NOT EXISTS(
SELECT 1
FROM dbo.Candidate AS ci
CROSS JOIN dbo.Skill AS s
LEFT JOIN dbo.CandidateSkills AS cs ON ci.candidate_id = cs.candidate_id AND s.skill_id = cs.skill_id
WHERE cs.candidate_id IS NULL
AND ci.candidate_id = c.candidate_id
AND s.skill_id IN(1,2,4)); --This could use a multiple valued parameter from SSRS



Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?


How to post data/code on a forum to get the best help: Option 1 / Option 2
Jason A. Long
Jason A. Long
SSChampion
SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)

Group: General Forum Members
Points: 13802 Visits: 6745
pietlinden - Thursday, February 8, 2018 10:56 AM
Jason,
Wow. I'll take a while to get my head around all this. Say I'm using SSRS as a front end to this - Essentially "Show me all candidates that have this list of skills" In SSRS, I can make the skill list a multi-select, so how would that part work? I can get a count of items selected really easily, so I can pass a value instead of a hard-coded 2. Or is this now an SSRS question?
Thanks!
Pieter

I was wandering about that... In any case, it only requires a minor change to the code (see below). In your OP you mentioned DelimitedSplit8K so I won't go into details about it or where to get it.
There are only 3, very simple,changes that need to be made...
#1 use a single CSV parameter rather than separate parameters.
#2 in the 1st CTE, split the SSV parameter with the help of DelimitedSplit8K.
#3 in the WHERE clause, use "LEN(@_mv_skill_param) - LEN(REPLACE(@_mv_skill_param, ',', '')) + 1" to determine the number of elements in the CSV
... super easy...

USE tempdb;
GO

IF OBJECT_ID('tempdb.dbo.Skill', 'U') IS NULL
BEGIN -- DROP TABLE tempdb.dbo.Skill;
CREATE TABLE tempdb.dbo.Skill (
skill_id INT NOT NULL IDENTITY(1,1),
skill_name VARCHAR(30) NOT NULL
CONSTRAINT pk_Skill PRIMARY KEY CLUSTERED (skill_id),
CONSTRAINT uq_Skill_skillname UNIQUE (skill_name)
);

INSERT tempdb.dbo.Skill (skill_name) VALUES ('C#'), ('.net'), ('C++'), ('T-SQL'), ('PL SQL');
END;

IF OBJECT_ID('tempdb.dbo.Candidate', 'U') IS NULL
BEGIN -- DROP TABLE tempdb.dbo.Candidate;
CREATE TABLE tempdb.dbo.Candidate (
candidate_id INT NOT NULL IDENTITY(1,1),
first_name VARCHAR(30) NOT NULL,
last_name VARCHAR(30) NOT NULL,
CONSTRAINT pk_Candidate PRIMARY KEY CLUSTERED (candidate_id)
);

INSERT tempdb.dbo.Candidate (first_name, last_name) VALUES
('Bob', 'Jones'), ('Mary', 'Baxter'), ('Kevin', 'Hall'), ('John', 'Doe'),
('Alison', 'Chains'), ('Molly', 'Hatchet'), ('Maxwell', 'House'), ('Red', 'Fox');
END;

IF OBJECT_ID('tempdb.dbo.CandidateSkills', 'U') IS NULL
BEGIN -- DROP TABLE tempdb.dbo.CandidateSkills;
CREATE TABLE tempdb.dbo.CandidateSkills (
candidate_id INT NOT NULL,
skill_id INT NOT NULL,
proficiency_level INT NOT NULL
CONSTRAINT ck_CandidateSkills_proficiencylevel CHECK (proficiency_level BETWEEN 1 AND 5),
CONSTRAINT pk_CandidateSkills PRIMARY KEY CLUSTERED (candidate_id, skill_id)
);

INSERT tempdb.dbo.CandidateSkills (candidate_id, skill_id, proficiency_level) VALUES
(1, 1, 3), (1, 2, 2), (1, 5, 4), (2, 1, 4), (2, 3, 2), (2, 4, 1),
(3, 4, 5), (3, 1, 1), (4, 5, 5), (5, 1, 2), (5, 2, 2), (5, 3, 3),
(5, 4, 1), (5, 5, 2), (6, 2, 4), (6, 4, 4), (7, 1, 3), (7, 3, 3),
(6, 5, 2), (7, 5, 5), (8, 1, 4), (8, 4, 4);
END;

--SELECT * FROM dbo.Candidate c;
--SELECT * FROM dbo.Skill s;
--SELECT * FROM dbo.CandidateSkills cs;

--=======================================================================================

DECLARE
@_mv_skill_param VARCHAR(8000) = '1,3,4'; --<< using a single CSV parameter... compatible with SSRS MV parameter output.

WITH
cte_required_skills AS (
SELECT
skill_id = ds.Item
FROM
dbo.DelimitedSplit8K(@_mv_skill_param, ',') ds
),
cte_cs_eval AS (
SELECT
cs.candidate_id,
cs.skill_id,
cs.proficiency_level,
skill_count = COUNT(1) OVER (PARTITION BY cs.candidate_id),
total_proficiency = SUM(cs.proficiency_level) OVER (PARTITION BY cs.candidate_id)
FROM
cte_required_skills rs
JOIN dbo.CandidateSkills cs
ON rs.skill_id = cs.skill_id
)
SELECT
c.candidate_id,
c.first_name,
c.last_name,
s.skill_name,
ce.proficiency_level
FROM
cte_cs_eval ce
JOIN dbo.Candidate c
ON ce.candidate_id = c.candidate_id
JOIN dbo.Skill s
ON ce.skill_id = s.skill_id
WHERE
ce.skill_count = LEN(@_mv_skill_param) - LEN(REPLACE(@_mv_skill_param, ',', '')) + 1 --<< counts the delimiters and then adds 1
ORDER BY
ce.total_proficiency DESC;


HTH,
Jason

pietlinden
pietlinden
SSC-Forever
SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)

Group: General Forum Members
Points: 42736 Visits: 16085
Super cool, Luis! Many graces!
This worked:
SELECT    candidate_id, first_name, last_name
FROM Candidate AS c
WHERE (NOT EXISTS
(SELECT 1 AS Expr1
FROM Candidate AS ci CROSS JOIN
Skill AS s LEFT OUTER JOIN
CandidateSkills AS cs ON ci.candidate_id = cs.candidate_id AND s.skill_id = cs.skill_id
WHERE (cs.candidate_id IS NULL) AND (ci.candidate_id = c.candidate_id) AND (s.skill_id IN (@prmSkills))))


Then the parameter, @prmSkills is multi-valued -
SELECT    skill_id, skill_name
FROM Skill
ORDER BY skill_name


Works a champ!
Joe Celko
Joe Celko
SSCarpal Tunnel
SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)

Group: General Forum Members
Points: 4259 Visits: 2916
pietlinden - Wednesday, February 7, 2018 6:46 PM


https://www.red-gate.com/simple-talk/sql/t-sql-programming/divided-we-stand-the-sql-of-relational-division/

https://www.red-gate.com/simple-talk/sql/learn-sql-server/high-performance-relational-division-in-sql-server/

http://publications.sqltopia.com/Relational%20Division.pdf

Please post DDL and follow ANSI/ISO standards when asking for help.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum








































































































































































SQLServerCentral


Search