How can I make a query to print sold out products? [MYSQL]

user_15556997 0
Viewed 47

I'm new to databases. So, I got two tables product and supplier, and I need to make a query that prints the sold out products alongside some supplier information.

product table:

Product_ID Product_Name Quantity Supplier_ID
1 water 0 11
2 Milk 26 12
3 eggs 8 12
4 5L water 19 11
5 water gallon 0 11

supplier table:

Supplier_ID Supplier_Name Supplier_Phone
11 Pure life 55555555
22 Dairy 77777777
33 Nivea 66666666

My work so far:

SELECT product.Product_ID, 'product.Product_Name', product.Quantity,
'supplier.Supplier_Name', supplier.Supplier_Phone
FROM product, supplier
INNER JOIN supplier S ON supplier.Supplier_ID = product.Supplier_ID
WHERE (Quantity = 0)
GROUP BY 'product.Product_Name';

The output:

Error Code: 1054. Unknown column 'product.Supplier_ID' in 'on clause'

the desired output:

Product_Name Quantity Supplier_Name Supplier_Phone
water 0 Pure life 55555555
water gallon 0 Pure life 55555555
mysql sql database
1 Answers

There are the following issues in your query:

  • in the SELECT clause, quotes will make MySQL understand you want some strings instead of referencing table fields: substitute backticks in place of quotes (or you can skip them completely in your case)
  • in the FROM clause, there are references to three tables product, supplier INNER JOIN supplier S, instead what you need is put the word INNER JOIN between the names of the two tables product and supplier
  • the GROUP BY clause, is not needed if you're not using an aggregation function (like MAX, SUM, GROUP_CONCAT, etc...) inside the SELECT statement

Here's a snippet of how your query should look like:

SELECT 
    product.Product_ID, 
    `product.Product_Name`, 
    product.Quantity,
    `supplier.Supplier_Name`, 
    supplier.Supplier_Phone
FROM 
    product 
INNER JOIN 
    supplier 
ON 
    supplier.Supplier_ID = product.Supplier_ID
WHERE 
    product.Quantity = 0
user_124928900
Related