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

1

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 ?

answered Aug 14, 2015 at 22:19

FrereDePute

2381 gold badge2 silver badges7 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Progs Over a year ago

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

1

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.

edited Aug 14, 2015 at 22:34

answered Aug 14, 2015 at 22:17

Vamsi Prabhala

49.4k4 gold badges41 silver badges64 bronze badges

2 Comments

Progs Over a year ago

I just tried that query and this the error: "Syntax error (missing operator) in query expression ''. " This leaves me more confused

Vamsi Prabhala Over a year ago

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

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related