On Fri, Dec 19, 2008 at 1:03 PM, Tompkins Neil <neil.tompk...@googlemail.com> wrote: > Hi, > > I've the following query which I'm having problems with. Basically I have > a 5 tables as follows : > > MasterTB - Contains list of master records > LookupTB - Contains relationship between MasterTB to ProductTB > ContentTB - Contains description of product, and location of data files > PriceTB - Contains list of prices per day for each product > ProductTB - List of products > > SELECT MasterTB.MasterID, ProductTB.Supplier, MasterTB.Name, > ContentTB.Title, ContentTB.FolderName, MIN(PriceTB.Price) As PriceDiscounts > FROM MasterTB > INNER JOIN LookupTB ON LookupTB.MasterID = MasterTB.MasterID > INNER JOIN ProductTB ON LookupTB.ProductID = ProductTB.ProductID > INNER JOIN PriceTB ON ProductTB.ProductID = PriceTB.ProductID > INNER JOIN ContentTB ON ProductTB.ProductID = ContentTB.ProductID > WHERE MasterTB.Enabled = 'Yes' > AND ContentTB.Language = 'ENG' AND ContentTB.Site = 'www' > AND PriceTB.Price > 0 > AND PriceTB.Quantity > 0 > GROUP BY ProductTB.ProductID, MasterTB.MasterID > ORDER BY ProductTB.MarkUp DESC > > Basically each product is listed in the master table, and can have a number > of suppliers linked to it (ProductTB). The query above will show me a list > of products for all suppliers for a particular product. However I want to > be able to show the lowest price product from just the lowest priced > supplier. > > Any ideas ? > > Thanks, > Neil >
You are actually going to need at least 2 queries, which will be nested. You need to first find the lowest price, then figure out which supplier has that lowest price. If more than one supplier has the same lowest price, you won't be able to do it in a single query and will likely need to do post processing. Just an example to point you in the right direction. First, get the lowest price for the product(s) you are interested in: SELECT PriceTB.ProductID, MIN(PriceTB.Price) As MinPrice FROM PriceTB GROUP BY ProductID Then you use that as a "virtual" table (MinPriceList) to join on the supplier with that price for that product. SELECT ProductTB.Supplier, MinPriceList.ProductID, MinPriceList.MinPrice As PriceDiscounts FROM MasterTB INNER JOIN ProductTB ON LookupTB.ProductID = ProductTB.ProductID INNER JOIN ( SELECT PriceTB.ProductID, MIN(PriceTB.Price) As MinPrice FROM PriceTB GROUP BY ProductID ) AS MinPriceList ON ProductTB.ProductID=MinPriceList.ProductID AND ProductTB.Price=MinPriceList.MinPrice INNER JOIN ... Basically what you are doing is creating a virtual table on the fly based on a select statement. It sort of like a temp table, but without having to go through the creation and management of it. Treat the virtual table created from the query as if it was a regular table. As I mentioned, this will break if more than one supplier has the same price. You'll get an arbitrary supplier ID out of those with the minimum price. This is because there is no unique value to join on. Hope that points you in the right direction. Brent Baisley -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
