Fetch row from table which matches a value in one column and presence of sub-string in comma separated string in other column

Question

Question How to find rows which matches value in one column ( using = operator) and other column should have a substring (using find_in_set or other substring match).

Scenario : I have three mysql tables:

1. Figures: It have details of figures like id, name, create-by, created-on, modified-on, status, etc. for ease I have mentioned only two columns:

 id |  name 
 1  | red green yellow circle
 2  | red square in yellow circle
 3  | 3D yellow red trapezium

2. Attributes : It stores different attributes with all possible comma separated values for each attributes.

id  |  term     | value
 1  |  shape    | circle,square,rectangle,parallelogram,trapezium
 2  |  color    | red,green,yellow,blue,white,orange
 3  | dimension | 1D,2D,3D

3. Figure Attribute Mapping : It stores mapping of figure, attributes and only those comma separated values which are applicable to that particular figure-attribute combination.

id | figure_id | attribute_id | value
 1 | 1         | 1            | circle
 2 | 1         | 2            | red,green,yellow
 3 | 2         | 1            | circle,square
 4 | 2         | 2            | red,yellow
 5 | 3         | 1            | trapezium
 6 | 3         | 2            | yellow,red
 7 | 3         | 3            | 3D

Objective : I want to write a query which returns me figure_id from figure_attribute_mapping table on matching attribute value in that atribute-figure mapping row.

Case I : search for square shape figure. My Query :

Select figure_id from figure_attribute_mapping 
where (attribute_id = 1 AND find_in_set('square',value)<>0);

Expected Answer : 2 Result : Positive

Case II : search for red color figure. My Query :

select figure_id from figure_attribute_mapping 
where (attribute_id = 2 AND find_in_set('red',value))

Expected Answer : 1, 2, 3 Result : Positive

Case III : search for red color square figure. My Query :

select figure_id from figure_attribute_mapping 
where 
      (attribute_id = 1 AND find_in_set('square',value)<>0) 
  AND (attribute_id = 2 AND find_in_set('red',value)<>0)

Expected Answer : 2 Result : negative

Case IV : search for red square in yellow circle figure. My Query :

 select figure_id from figure_attribute_mapping 
where
  ( 
       (attribute_id = 1 AND find_in_set('square',value)<>0) 
  AND  (attribute_id = 1 AND find_in_set('circle',value)<>0)
  ) 
AND
 ( 
       (attribute_id = 2 AND find_in_set('red',value) <>0) 
  AND (attribute_id = 2 AND find_in_set('yellow',value)<>0) 
 )

Expected Answer : 2 Result : negative

I am able to find figure_id when attribute type is similar, but can't find figure_id when multiple attributes comes into question.

Can someone please help in creating a mysql query.

Answer 1

One solution is to group your rows by figure_id and search on attribute value by concatenating it.

Below are the queries for each of your cases:

Case I:

SELECT z.* FROM (
    SELECT id, figure_id, GROUP_CONCAT(value) AS merged_value
    FROM figure_attribute_mapping
    GROUP BY figure_id
) z
WHERE FIND_IN_SET('square', z.merged_value);

Output

id     | figure_id | merged_value
+------+-----------+--------------------------+
|    3 |         2 | circle,square,red,yellow

Case II:

SELECT z.* FROM (
    SELECT id, figure_id, GROUP_CONCAT(value) AS merged_value
    FROM figure_attribute_mapping
    GROUP BY figure_id
) z
WHERE FIND_IN_SET('red', z.merged_value);

Output

| id   | figure_id | merged_value             |
+------+-----------+--------------------------+
|    1 |         1 | circle,red,green,yellow  |
|    3 |         2 | circle,square,red,yellow |
|    5 |         3 | trapezium,yellow,red,3D  |

Case III:

SELECT z.* FROM (
    SELECT id, figure_id, GROUP_CONCAT(value) AS merged_value
    FROM figure_attribute_mapping
    GROUP BY figure_id
) z
WHERE FIND_IN_SET('square', z.merged_value)
AND FIND_IN_SET('red', z.merged_value);

| id   | figure_id | merged_value             |
+------+-----------+--------------------------+
|    3 |         2 | circle,square,red,yellow |

Case IV:

SELECT z.* FROM (
    SELECT id, figure_id, GROUP_CONCAT(value) AS merged_value
    FROM figure_attribute_mapping
    GROUP BY figure_id
) z
WHERE FIND_IN_SET('square', z.merged_value)
AND FIND_IN_SET('circle', z.merged_value)
AND FIND_IN_SET('red', z.merged_value)
AND FIND_IN_SET('yellow', z.merged_value);

Output

| id   | figure_id | merged_value             |
+------+-----------+--------------------------+
|    3 |         2 | circle,square,red,yellow |