Re: Distinct Query Problem
Hi Sonal, What information do you require ? Basically in the MasterTB we have productID and name, description. In the LookupTB we have the productID from the MasterTB, the productID from ProductTB and a char field for preferred supplier Yes/No. In the ProductTB we have a list of products from each supplier. This is link using the LookupTB. Let me know what other information you require. Thanks Neil On Tue, Dec 23, 2008 at 4:19 AM, Raghani, Sonal (IE10) sonal.ragh...@honeywell.com wrote: Hi, The problem statement needs to be elaborated. Please give the exact columns interms of wat is suppliers which table is it coming from etc. -Original Message- From: Tompkins Neil [mailto:neil.tompk...@googlemail.com] Sent: Monday, December 22, 2008 5:16 PM To: Baron Schwartz; [MySQL] Subject: Re: Distinct Query Problem Hi, If anyone could point me in the right direction, I'd be most grateful. Thanks ! Neil On Mon, Dec 22, 2008 at 9:55 AM, Tompkins Neil neil.tompk...@googlemail.com wrote: Hi I'm having trouble trying to figure this out. Any help/example would be grateful. Thanks Neil On Sun, Dec 21, 2008 at 4:30 PM, Baron Schwartz ba...@xaprb.com wrote: On Fri, Dec 19, 2008 at 1:03 PM, Tompkins Neil neil.tompk...@googlemail.com wrote: 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. http://jan.kneschke.de/projects/mysql/groupwise-max http://www.xaprb.com/blog/2006/12/07/how-to-select-the-firstleastmax-row -per-group-in-sql/ -- Baron Schwartz, Director of Consulting, Percona Inc. Our Blog: http://www.mysqlperformanceblog.com/ Our Services: http://www.percona.com/services.html
Re: Distinct Query Problem
Hi I'm having trouble trying to figure this out. Any help/example would be grateful. Thanks Neil On Sun, Dec 21, 2008 at 4:30 PM, Baron Schwartz ba...@xaprb.com wrote: On Fri, Dec 19, 2008 at 1:03 PM, Tompkins Neil neil.tompk...@googlemail.com wrote: 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. http://jan.kneschke.de/projects/mysql/groupwise-max http://www.xaprb.com/blog/2006/12/07/how-to-select-the-firstleastmax-row-per-group-in-sql/ -- Baron Schwartz, Director of Consulting, Percona Inc. Our Blog: http://www.mysqlperformanceblog.com/ Our Services: http://www.percona.com/services.html
Re: Distinct Query Problem
Hi, If anyone could point me in the right direction, I'd be most grateful. Thanks ! Neil On Mon, Dec 22, 2008 at 9:55 AM, Tompkins Neil neil.tompk...@googlemail.com wrote: Hi I'm having trouble trying to figure this out. Any help/example would be grateful. Thanks Neil On Sun, Dec 21, 2008 at 4:30 PM, Baron Schwartz ba...@xaprb.com wrote: On Fri, Dec 19, 2008 at 1:03 PM, Tompkins Neil neil.tompk...@googlemail.com wrote: 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. http://jan.kneschke.de/projects/mysql/groupwise-max http://www.xaprb.com/blog/2006/12/07/how-to-select-the-firstleastmax-row-per-group-in-sql/ -- Baron Schwartz, Director of Consulting, Percona Inc. Our Blog: http://www.mysqlperformanceblog.com/ Our Services: http://www.percona.com/services.html
Re: Distinct Query Problem
OK, I've made further progress by changing GROUP BY ProductTB.ProductID, MasterTB.MasterID to GROUP BY MasterTB.MasterID. However ProductTB.Supplier is showing the incorrect Supplier. Why is this ? Thanks Neil On Mon, Dec 22, 2008 at 11:45 AM, Tompkins Neil neil.tompk...@googlemail.com wrote: Hi, If anyone could point me in the right direction, I'd be most grateful. Thanks ! Neil On Mon, Dec 22, 2008 at 9:55 AM, Tompkins Neil neil.tompk...@googlemail.com wrote: Hi I'm having trouble trying to figure this out. Any help/example would be grateful. Thanks Neil On Sun, Dec 21, 2008 at 4:30 PM, Baron Schwartz ba...@xaprb.comwrote: On Fri, Dec 19, 2008 at 1:03 PM, Tompkins Neil neil.tompk...@googlemail.com wrote: 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. http://jan.kneschke.de/projects/mysql/groupwise-max http://www.xaprb.com/blog/2006/12/07/how-to-select-the-firstleastmax-row-per-group-in-sql/ -- Baron Schwartz, Director of Consulting, Percona Inc. Our Blog: http://www.mysqlperformanceblog.com/ Our Services: http://www.percona.com/services.html
Re: Distinct Query Problem
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
Distinct Query Problem
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
Re: Distinct Query Problem
On Fri, Dec 19, 2008 at 7:03 PM, Tompkins Neil wrote: 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. Search the manual for group-wise maximum. Jochem -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
