CODEWITHSUNDEEP
mysql
Niborb
Niborb

Reputation: 784

How to get the intersection of two columns

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}

Upvotes: 4

Views: 4237

Answers (3)

MvG
MvG

Reputation: 60968

If your column is of type SET, then it is stored as a number internally, and will auto-convert to that number where appropriate. The operations you describe correspond to bit-wise logical operations on those numbers. For example, the intersection can be computed using the bit-wise and of the values from two columns.

a.data & b.data AS intersection,
(a.data & b.data) <> 0 AS aAndBIntersect,
(a.data & b.data) == b.data AS bIsSubsetOfA

This requires that the type of both columns is the same, so that the same strings correspond to the same bits. To turn the result back into a string, you'd could use ELT, but with all the combination that's likely to get ugly. As an alternative, you could save the result in a temporary table with the same data type, storing it as a number and later retrieving it as a string.

Upvotes: 1

Omesh
Omesh

Reputation: 29111

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;

Upvotes: 4

MvG
MvG

Reputation: 60968

You get the intersection from an inner join:

SELECT a.data FROM a, b WHERE a.data = b.data

To decide whether b is a subset of a, you can do

SELECT b.data FROM b LEFT JOIN a ON a.data = b.data WHERE a.data IS NULL

This will compute the difference: all values from b which are not contained in a. If it is empty, then b is a subset of a.

You can use both of these approaches as subqueries inside a larger query.

Upvotes: 1

Related Questions