How to simulate a N-dimension INTERSECT with generic SQL-CodePudding

Here is an example of 2 extract from the same table:

SELECT source_id 
FROM table_cust_string_value
WHERE cust_string_id=2 AND VALUE LIKE '%TATA%';

SELECT source_id 
FROM table_cust_string_value
WHERE cust_string_id=4 AND VALUE LIKE '%TUTU%';

They give 2 sets of source_id.

Right. Now if I need an intersect of those with MySQL (where INTERSECT does not exist) I found this way:

SELECT DISTINCT source_id 
FROM (
   SELECT source_id 
   FROM table_cust_string_value
   WHERE cust_string_id=2 AND VALUE LIKE '%TATA%'
   ) t1
INNER JOIN (
   SELECT source_id 
   FROM table_cust_string_value
   WHERE cust_string_id=4 AND VALUE LIKE '%TUTU%'
   ) t2
USING (source_id);

but what if I need to do this from N sets ?

I can't find a solution I'm worried about the perf. of doing it this way

CodePudding user response：

You can use a grouping approach. Depending on what indexes you have available this might work out better.

SELECT source_id
FROM   table_cust_string_value
WHERE  cust_string_id IN ( 2, 4 )
GROUP  BY source_id
HAVING MAX(CASE WHEN cust_string_id = 2 AND VALUE LIKE '%TATA%' THEN 1 END) = 1
       AND MAX(CASE WHEN cust_string_id = 4 AND VALUE LIKE '%TUTU%' THEN 1 END) = 1