CODEWITHSUNDEEP
mysql
Rainbard
Rainbard

Reputation: 341

Find elements in a set that are not in another set using native MySQL functions

I have two variables having comma separated IDs.

SET @Set1 = '1,2,3,4';
SET @Set2 = '3,2,5,6';

I want to get all elements in Set1 that are not in Set2 using just MySQL functions. In the above case, the answer is: '1,4'.

Please note that I want to use only native MySQL functions.

Upvotes: 0

Views: 124

Answers (1)

John Ruddell
John Ruddell

Reputation: 25842

the easiest way would be to normalize your set you want to find the results in and then use FIND_IN_SET() on the second set like so

SET @Set1 = '1,2,3,4';
SET @Set2 = '3,2,5,6';

SELECT col 
FROM
(   SELECT
        SUBSTRING_INDEX(SUBSTRING_INDEX(@Set1, ',', n.digit+1), ',', -1) col
    FROM (SELECT @set1) temp
    JOIN(SELECT 0 digit UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3) n
        ON LENGTH(REPLACE(@Set1, ',' , '')) <= LENGTH(@Set1)-n.digit
    ORDER BY n.digit
) t 
WHERE NOT FIND_IN_SET(col, @set2);

if you want to capture as many comma separated digits as you can then just do this

SELECT col 
FROM
(   SELECT
        SUBSTRING_INDEX(SUBSTRING_INDEX(@Set1, ',', n.digit+1), ',', -1) col
    FROM (SELECT @set1) temp
    JOIN
    (   SELECT
            SEQ.SeqValue as digit
        FROM
        (   SELECT (HUNDREDS.SeqValue + TENS.SeqValue + ONES.SeqValue) SeqValue
            FROM(SELECT 0  SeqValue UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) ONES
            CROSS JOIN(SELECT 0 SeqValue UNION ALL SELECT 10 UNION ALL SELECT 20 UNION ALL SELECT 30 UNION ALL SELECT 40 UNION ALL SELECT 50 UNION ALL SELECT 60 UNION ALL SELECT 70 UNION ALL SELECT 80 UNION ALL SELECT 90) TENS
            CROSS JOIN(SELECT 0 SeqValue UNION ALL SELECT 100 UNION ALL SELECT 200 UNION ALL SELECT 300 UNION ALL SELECT 400 UNION ALL SELECT 500 UNION ALL SELECT 600 UNION ALL SELECT 700 UNION ALL SELECT 800 UNION ALL SELECT 900) HUNDREDS
        ) SEQ
    ) n
        ON LENGTH(REPLACE(@Set1, ',' , '')) <= LENGTH(@Set1)-n.digit
    ORDER BY n.digit
) t 
WHERE NOT FIND_IN_SET(col, @set2);

which will return up to 1000 items in a single comma separated list

Upvotes: 1

Related Questions