There are two tables, with a many-to-many relationship.

  1. The first table is Products,
  2. The second table is Ingredients.
  3. The third table is ProductsIngredients

Products (ID, Name)
Ingredients (ID, Name)
ProductsIngredients (ID, ProductID, IngredientID)

Accordingly: products can have many ingredients, and ingredients can have many products in which they are included.

It is required to obtain from the database only those products that contain, for example, both milk, millet, and sugar (and the order of indication may be chaotic). I don’t understand how to do this on the DB side, but dragging huge tables into RAM and processing on the client side is not an option, as it seems to me.

I ask for help, thanks in advance)

    1 answer 1

    If I understand correctly, then you need a request that will return products that have all 3 ingredients:

    select p.name from Products p join ProductsIngredients pit on p.id = pit.ProductID join Ingredients i on i.id = pit.IngredientID where i.name in ('Молоко', 'Сахар', 'Пшено') group by p.name having count(distinct i.id) = 3
    • Yes, it does, thanks. Just explain 1 point about what the last line is doing .. Since I’ve figured out the others before, but the query for "in" would output all products containing either milk, or sugar, or millet) - Nikita
    • Oh, and more. How to pull out the whole Product, without listing after the select what I need to pull out? - Nikita
    • @Nikita we impose a condition so that the product has only 3 ingredients - milk, sugar and millet, and then we look at how many of them are unique. if all three are unique, then there is milk, sugar, and millet, if 2, then there is not 1 of any ingredient. I did not understand the second comment a little) - Denis
    • on the second question - I need to get not only the name of the product, but all the other fields (ID, Category, Description, ... there are a lot of them. I just indicated the ID and Name in the question for simplicity). And here we get only Name (select p.name) .. But if I try to make select p. * I get the error 'Column' Products. ID 'is invalid function or the GROUP BY clause. ' - Nikita
    • @Nikita if you need the remaining fields only from the Products table, then write them separated by commas, and add all the fields in group by . Either wrap the query in a subquery and attach the products table to it again, there it is already possible with p. * - Denis