Syntax error on inner join

Question

My tables on MySQL

Table Suppliers

--------------------------------------------------- SupplierID | SupplierName | ContactName Address ---------------------------------------------------

Table Products

----------------------------------------------- ProductID | ProductName |SupplierID Unit| Price -----------------------------------------------

The Query

SELECT Suppliers.SupplierName , ProductName , Price FROM Products INNER JOIN (SELECT SupplierID , MAX(Price) AS maxPrice FROM Products GROUP BY SupplierID) as gp ON Products.Price = gp.maxPrice INNER JOIN Suppliers ON Suppliers.SupplierID = Products.SupplierID;

What im trying to do is to obtain is the name of the product, the name of the supplier and their respective prices, based on the price of the most expensive product of each supplier (that's what is the subquery is extracting), but the rest of query doesn't work, not sure why

the SQL error

Syntax error (missing operator) in query expression 'Products.Price = gp.maxPrice INNER JOIN Suppliers ON Suppliers.SupplierID = Products.SupplierID'.

FrereDePute · Accepted Answer · 2015-08-14 22:19:02Z

SELECT Suppliers.SupplierName,ProductName,Price FROM (Products INNER JOIN (SELECT SupplierID, MAX(Price) AS maxPrice FROM Products GROUP BY SupplierID) as gp ON Products.Price = gp.maxPrice) INNER JOIN Suppliers ON Suppliers.SupplierID = Products.SupplierID;

Does it work ?

Yes!! this one displays the data i need, i'm trying to understand why the parenthese are needed thou

Vamsi Prabhala · Accepted Answer · 2015-08-14 22:34:32Z

SELECT Suppliers.SupplierName,ProductName,Price FROM Products INNER JOIN (SELECT SupplierID, MAX(Price) maxPrice FROM Products GROUP BY SupplierID) gp ON Products.Price = gp.maxPrice and products.supplierid = gp.supplierid INNER JOIN Suppliers ON Suppliers.SupplierID = Products.SupplierID;

You can try this. The join has also been done on supplierid for the subquery. Also make sure you include tablenames before column names in the select clause, to avoid ambiguity.

I just tried that query and this the error: "Syntax error (missing operator) in query expression ''. " This leaves me more confused
try this..removed as in the sub-query. not sure what difference it would make.

Collectives™ on Stack Overflow

Syntax error on inner join

2 Answers 2

1 Comment

2 Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

2 Comments

Related