Relational Division With Remainder: Splitting Comma-Separated Data and Checking with Another Table

In this article, we’ll explore a common SQL problem involving comma-separated data and checking it against another table. We’ll dive into the world of relational division with remainder, discuss different solutions, and provide code examples to illustrate each approach.

Problem Statement

Suppose you have a table with two fields: TagID and ProspectID. The TagID field contains comma-separated values, while the ProspectID field stores individual prospect IDs. Your goal is to return all prospect IDs that are associated with at least one of the comma-separated values in the TagID column.

For example, if you have a table like this:

CREATE TABLE #Temp (
    TagID       NVARCHAR(MAX),
    ProspectID  INT
);

INSERT INTO #Temp(TAGID,ProspectID)
VALUES 
('20,30,40',1),
('30,50',2),
('20,30,40',3),
('60,70',4),
('30',5);

You want to return the prospect IDs that are associated with the values ‘30’ and ‘40’. In other words, you want to find all prospect IDs that contain at least one of these comma-separated values.

Relational Division With Remainder

Relational division with remainder is a SQL technique used to solve problems involving multiple conditions on a single column. The basic idea is to use subqueries or common table expressions (CTEs) to break down the data into smaller, more manageable pieces.

In our case, we can use the STRING_SPLIT function to split the comma-separated values in the TagID column and then join it with another table or CTE that contains the filtered values.

Solution 1: Using STRING_SPLIT

Here’s one common solution using STRING_SPLIT:

CREATE TABLE #Temp (
    TagID       NVARCHAR(MAX),
    ProspectID  INT
);

INSERT INTO #Temp(TAGID,ProspectID)
VALUES 
('20,30,40',1),
('30,50',2),
('20,30,40',3),
('60,70',4),
('30',5);

DECLARE @Filter NVARCHAR(251) ='30,40';

SELECT
  t.ProspectID
FROM #Temp t
WHERE EXISTS (SELECT 1
    FROM STRING_SPLIT(@Filter, ',') f
    LEFT JOIN STRING_SPLIT(t.TagID, ',') t2 ON t2.value = f.value
    HAVING COUNT(t2.value) = COUNT(f.value)  -- none missing
);

This query splits the @Filter string into individual values and joins it with the split values from the TagID column. The HAVING COUNT clause ensures that only rows with no missing values are returned.

Solution 2: Using a Separate Table or CTE

However, your schema design is flawed. Instead of storing multiple pieces of information in one column, you should store them in separate rows. Here’s an alternative solution using a separate table or CTE:

CREATE TABLE #Temp (
    TagID       int,
    ProspectID  int
);

INSERT INTO #Temp (TagID, ProspectID)
VALUES
(20, 1),
(30, 1),
(40, 1),
(30, 2),
(50, 2),
(20, 3),
(30, 3),
(40, 3),
(60, 4),
(70, 4),
(30, 5);

DECLARE @Filter TABLE(value int PRIMARY KEY);
INSERT @Filter (value) VALUES
(30),
(40);

DECLARE @totalFilters int = (SELECT COUNT(*) FROM @Filter);

SELECT
  t.ProspectID
FROM #Temp t
JOIN @Filter f ON t.TagID = f.value
GROUP BY
  t.ProspectID
HAVING COUNT(*) = @totalFilters;  -- none missing

In this solution, we create a separate table or CTE to store the filtered values and then join it with the #Temp table. The GROUP BY clause ensures that only rows with no missing values are returned.

Conclusion

Relational division with remainder is a powerful SQL technique used to solve problems involving multiple conditions on a single column. By using subqueries, CTEs, or other techniques, you can break down complex data into smaller pieces and join it with another table or data source.

In this article, we’ve explored two common solutions for splitting comma-separated data and checking it against another table. We’ve discussed the importance of schema design and presented alternative approaches that store multiple values in separate rows.

By applying these techniques and understanding the underlying principles of relational division with remainder, you can write more efficient and effective SQL queries to solve complex problems in your database.

Last modified on 2023-06-01