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
