0

I have 3 Tables: The first Contains Products:

+-----+-------------+---------------+
|  ID |     NAME    | IngredientsID |
+-----+-------------+---------------+
| 01  |   Pizza     | 01,02,04      |
+-----+-------------+---------------+
| 02  |   Spagetti  | 01,02         |
+-----+-------------+---------------+
| 03  | Cheesburger | 01,04,02,03   |
+-----+-------------+---------------+
| 04  |   Nuggets   | 01,04         |
+-----+-------------+---------------+

The second contains Ingredients Details:

+-----+-------------+-------------+--------+
|  ID |     NAME    |  Allergens  | Frozen |
+-----+-------------+-------------+--------+
| 01  |   Flour     |   01        |    0   |
+-----+-------------+-------------+--------+
| 02  |   Water     |             |    0   |
+-----+-------------+-------------+--------+
| 03  |   Nuts      |  03         |    0   |
+-----+-------------+-------------+--------+
| 04  |   Fish      |  02         |    1   |
+-----+-------------+-------------+--------+

The second contains Allergens details:

+-----+-------------+-------------+
|  ID |     NAME    |  Allergens  |
+-----+-------------+-------------+
| 01  |   Flour     |   01        |
+-----+-------------+-------------+
| 02  |   Water     |             |
+-----+-------------+-------------+
| 03  |   Nuts      |  03         |
+-----+-------------+-------------+
| 04  |   Fish      |  02         |
+-----+-------------+-------------+

now with a query I need to extract for a given Food_ID, the list of Ingredients' Names and Allergenes Names:

and I used the following query:

SELECT
    ID,
    Name,
    (
        select 
            GROUP_CONCAT(DISTINCT Name SEPARATOR ',')
        from Ingredients
        where (select IngredientsID from Products where ID=@Food_ID) 
        like concat('%',IngredientID,'%')
    ) IngredientsNames
FROM products 
WHERE ID=@Food_ID

(for allergenes Names I used a nested GROUP_CONCAT)

that return for @Food_ID=03 something like:

+-----+-------------+----------------------------+
| ID  |  Name       | IngredientsNames           |
+-----+-------------+----------------------------+
| 03  | Cheesburger | Water,Flour,Fish,Nuts      |
+-----+-------------+----------------------------+

I'm new with MYSQL, and I tried to use IN and also FIND_IN_SET operator but did not succeed: so I used LIKE: I think there is a better way to obtain the result, but it works although not perfectly:

in fact, Ingredients has to be indicated according to their quantity in the Dish: First the most present and so on, while the list of Ingredients give by the query contains ingredients name in random order, while it should follow the order stated in IngredientsID in Products Table: therefore 

Flour,Fish,Water,Nuts

Is there a way to achieve this?

Thanks

Joe
  • 1,033
  • 1
  • 16
  • 39
  • 1
    `FIND_IN_SET()` is the correct way to join the tables, see https://stackoverflow.com/questions/16208565/search-with-comma-separated-value-mysql/16210672#16210672 – Barmar Oct 05 '18 at 16:33
  • You can put `ORDER BY` in the `GROUP_CONCAT()` call to specify the ordering. But I don't see where you have quantity. – Barmar Oct 05 '18 at 16:34
  • You should stop using comma-separated lists and normalize your schema. – Barmar Oct 05 '18 at 16:35
  • @Barmar Yes, you're definitively right.. but this is what I have inherited: clearly could convert it but I was trying to find a solution with the actual tables – Joe Oct 05 '18 at 16:57
  • @Barmar Actually there are no quantity: `ORDER BY` should be by the sequence of the `IngredientsID` value that in the example is `01,04,02,03` therefore the result should be `Flour,Fish,Water,Nuts` – Joe Oct 05 '18 at 17:30
  • 1
    If you're using MySQL 8.0 you can use `REGEXP_INSTR()` to find the position of the ingredient ID in the comma-separated list, and order by that. If not, you'll need to write a stored function that finds the position, there's nothing built-in. – Barmar Oct 05 '18 at 18:40
  • Thanks alot for the hints.. unfortunately it is installed MySQL 5.7... therefore I will try to build a function counting the number of the commas... – Joe Oct 06 '18 at 17:01

0 Answers0