I need assistance with my MySql query (selecting from multiple tables)

bojan Опубликовано в Dev

Bojan

So, I have 3 tables:

PIZZA with pizza_ID, pizza_title, and pizza_price
INGREDIENTS with ingredients_ID; ingredients_title, and ingredients_description
PIZZA_INGREDIENTS with ID, pizza_id, ingredients_id

and I need to select all pizza_title from PIZZA that contains 2 ingredients. For now, I have this:

SELECT pizza.pizza_ID, pizza.pizza_title, pizza.pizza_price, pizza_ingredients.ingredient_id
FROM pizza
JOIN pizza_ingredients ON pizza_ingredients.pizza_id=pizza.id
WHERE pizza_ingredients.ingredient_id='7' or pizza_ingredients.id='4'
GROUP BY pizza.id

this code returns all pizzas that have ingredient 7 or ingredient 4. and I need only pizzas that have both 7 and 4...

SQL is not my strong side, and I appreciate any help. Thanks

Tim Biegeleisen

Try joining to a subquery which does an aggregation to find all pizzas having exactly two ingredients:

SELECT p1.*
FROM pizza p1
INNER JOIN
(
    SELECT pizza_id
    FROM pizza_ingredients
    GROUP BY pizza_id
    HAVING COUNT(*) = 2
) p2
    ON p1.pizza_id = p2.pizza_id;

If I misunderstood the wording of your question, and you instead want to find all pizzas having two particular ingredients, without regard to other ingredients, then use this query:

SELECT p1.*
FROM pizza p1
INNER JOIN
(
    SELECT pizza_id
    FROM pizza_ingredients
    WHERE ingredients_id IN (4, 7)
    GROUP BY pizza_id
    HAVING COUNT(DISTINCT ingredients_id) = 2
) p2
    ON p1.pizza_id = p2.pizza_id;

Эта статья взята из Интернета, укажите источник при перепечатке.

Если есть какие-либо нарушения, пожалуйста, свяжитесь с[email protected] Удалить.