How to get the intersection of two columns

mysql

Solution

You can take interection of tables using `INNER JOIN`

have a look at Visual explaination of joins

SELECT fn_intersect_string(a.data, b.data) AS result FROM table_name;

also you can write a user defined function as:

CREATE FUNCTION fn_intersect_string(arg_str1 VARCHAR(255), arg_str2 VARCHAR(255))
RETURNS VARCHAR(255)
BEGIN
    SET arg_str1 = CONCAT(arg_str1, ",");
    SET @var_result = "";

    WHILE(INSTR(arg_str1, ",") > 0)
    DO
        SET @var_val = SUBSTRING_INDEX(arg_str1, ",", 1);
        SET arg_str1 = SUBSTRING(arg_str1, INSTR(arg_str1, ",") + 1);

        IF(FIND_IN_SET(@var_val, arg_str2) > 0)
        THEN
            SET @var_result = CONCAT(@var_result, @var_val, ",");
        END IF;
    END WHILE;

    RETURN TRIM(BOTH "," FROM @var_result);
END;

Problem

For instance I have table A and table B a.data = {1,2,3,4,5,6} b.data = {4,5,7} If you want to lookup one value in a.data or b.data you can use FIND_IN_SET(3, b.data). But I want to know if at least all the values of b.data are in a.data, or else if I can find at least the intersection between b.data and a.data. So in this case {4,5}. WHERE INTERSECT(a.data, b.data) ... something like that. How should I do this in MySQL? update The b.data {4,5,7} is the column data of one 1 record, so joining a.data on b.data won't work. ``` table A ======= ID DATA 1 {1,2,3,4,5,6} 2 {7,9,12} table B ======= ID DATA 1 {4,5,7} 2 {9,10,11,12} ```

Original source