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.
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 |