2

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
lemon
  • 14,875
  • 6
  • 18
  • 38
AlwalidHash
  • 31
  • 5
  • Change `FROM product, supplier` to `FROM product` you are referencing the supplier in the JOIN you dont need to do both – RiggsFolly Apr 21 '22 at 14:58
  • And you use backticks and not single quotes around tabe/column names – RiggsFolly Apr 21 '22 at 15:00
  • Could you please add the desired output? It's unclear if you are using a group by you don't need or if you are missing to create a sum. You could if possible also add more rows to your example to make it more clear. Thank you. – Jonas Metzler Apr 21 '22 at 15:03

1 Answers1

1

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
lemon
  • 14,875
  • 6
  • 18
  • 38
  • still shows this: Error Code: 1054. Unknown column 'product.Product_Name' in 'field list' – AlwalidHash Apr 21 '22 at 15:03
  • There are some correct points noted in this answer, but I think the desired output must be made clear before giving an answer that is really helpful. – Jonas Metzler Apr 21 '22 at 15:05