Cloud Strife Cloud Strife - 15 days ago 8
MySQL Question

mysql #1242 - Subquery returns more than 1 row

This is for my thesis and the dead end is later i don't know what i do wrong here .. Im hoping that someone can help me to know what's wrong here thanks

SELECT
flower_id,
flower_name,
flower_description,
flower_price,
flower_category,
(quantity - (SELECT
SUM(q.quantity_value)
FROM
orders_details od
INNER JOIN
cart_details cd ON cd.cart_id = od.cart_id
INNER JOIN
quantities q ON q.quantity_id = cd.quantity_id
WHERE
od.flag = 1 AND cd.flower_id = flower_id
GROUP BY cd.flower_id)) AS 'quantity',
mfg_date,
exp_date
FROM
flower_details,
categories
WHERE
flower_details.flower_category = categories.category_id


What im doing here is getting the total quantity of products from customer bought minus to inventory stocks

Answer

If your subselect return more then a rows you should join the sum using an inner join on subselect If your subselect return more then a rows you should join the sum using an inner join on subselect inner join on subselect

      SELECT 
        flower_details.flower_id,
        flower_name,
        flower_description,
        flower_price,
        flower_category,
        flower_details.quantity - t1.quantity, 
        mfg_date,
        exp_date
    FROM flower_details 
    INNER JOIN categories ON flower_details.flower_category = categories.category_id
    INNER JOIN       (
                SELECT  cd.flower_id , 
                SUM(q.quantity_value) AS quantity
            FROM
                orders_details od
                    INNER JOIN
                cart_details cd ON cd.cart_id = od.cart_id
                    INNER JOIN
                quantities q ON q.quantity_id = cd.quantity_id
            WHERE
                od.flag = 1 AND cd.flower_id = flower_id
            GROUP BY cd.flower_id
    )  t1 on flower_details.flower_id = t1.flower_id