CODEWITHSUNDEEP
mysqlsql
Progs
Progs

Reputation: 737

Syntax error on inner join

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'.

Upvotes: 0

Views: 695

Answers (2)

Vamsi Prabhala
Vamsi Prabhala

Reputation: 49260

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.

Upvotes: 1

FrereDePute
FrereDePute

Reputation: 238

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 ?

Upvotes: 1

Related Questions