Question

1. List the ingredient id, name, date ordered, quantity & unit (in one column) for all ingredients, even if the ingredient has not been ordered (Hint: use outer join)

SELECT ingredient_id, ingredient_name, date_ordered, CONCAT(quantity, unit) AS "Quantity & Unit"
FROM ingredient ing LEFT OUTER JOIN ingredient_purchase_list ing2 ON ing.ingredient_id = ing2.ingredient_id
ORDER BY ingredient_id;

(It says column ambiguosly defined)

Answer 1

SELECT ingredient_id, ingredient_name, date_ordered, CONCAT(quantity, unit) AS "Quantity & Unit"
FROM ingredient ing LEFT OUTER JOIN ingredient_purchase_list ing2 ON ing.ingredient_id = ing2.ingredient_id
ORDER BY ingredient_id;

here ingredient_id is present in two tables ingredient and ingredient_purchase_list .so we need to differentiate from which table we are taking ingredient_id.

correct query is

SELECT ing.ingredient_id, ing.ingredient_name, date_ordered, CONCAT(quantity, unit) AS "Quantity & Unit"
FROM ingredient ing LEFT OUTER JOIN ingredient_purchase_list ing2 ON ing.ingredient_id = ing2.ingredient_id
ORDER BY ing ingredient_id;