| Groups | Blog | Home |
sql server (alternate) : SELECT DISTINCT with JOIN
[quoted text, click to view] > Have a problem I would areally appreciate help with. > I have 3 tables in a standard format for a Bookshop, eg > > Products > Categories > Categories_Products > > the latter allowing me to have products in multiple categories. > > Everthing works well except for one annoying little thing. > > When an individual product (which is in more than one topcategory) is > added to the Shopping Cart it displays twice, because in my select > statement I have the Category listed. I realise I could remove the > TopCategory from the statement and that makes my DISTINCT work as I > wanted, but Id prefer to have the TopCategory as it saves me later > having to another SQL query (Im already doing one to allow me not to > list category in the Statement .... but If I can overcome this one ... > then I can remove this as well). You want to display a distinct list, but you only want one TopCategory. So which one do you want? Well, assuming you only want the highest, you could do: SELECT a.idProduct, a.description,a.descriptionLong, a.listPrice,a.price,a.smallImageUrl,a.stock, a.fileName,a.noShipCharge, MAX(c.topcategory) FROM products a, categories_products b, categories c WHERE active = -1 AND homePage = -1 AND a.idProduct = b.idProduct AND c.idcategory=b.idcategory AND prodType = 1 ORDER BY a.idProduct DESC GROUP BY a.idProduct, a.description,a.descriptionLong, a.listPrice,a.price,a.smallImageUrl,a.stock, a.fileName,a.noShipCharge -- Erland Sommarskog, SQL Server MVP, sommar@algonet.se Books Online for SQL Server SP3 at
[quoted text, click to view]
On Fri, 14 May 2004 09:05:08 +1200, Craig Hoskin wrote: >Hi everyone > >Have a problem I would areally appreciate help with. >I have 3 tables in a standard format for a Bookshop, eg > > Products > Categories > Categories_Products > >the latter allowing me to have products in multiple categories. > >Everthing works well except for one annoying little thing. > >When an individual product (which is in more than one topcategory) is added >to the Shopping Cart it displays twice, because in my select statement I >have the Category listed. I realise I could remove the TopCategory from the >statement and that makes my DISTINCT work as I wanted, but Id prefer to have >the TopCategory as it saves me later having to another SQL query (Im already >doing one to allow me not to list category in the Statement .... but If I >can overcome this one ... then I can remove this as well). > >Here is my table structure (the necessary bits) >products > idProduct int > .... > >categories > idcategory int > idParentCategory int > topcategory int > ... > >categories_products > idCatProd int > idProduct int > idCategory > >When I run a query such as > >SELECT DISTINCT a.idProduct, a.description,a.descriptionLong, >a.listPrice,a.price,a.smallImageUrl,a.stock, a.fileName,a.noShipCharge, >c.topcategory >FROM products a, categories_products b, categories c >WHERE active = -1 AND homePage = -1 > AND a.idProduct = b.idProduct > AND c.idcategory=b.idcategory > AND prodType = 1 ORDER BY a.idProduct DESC > >This will return all products as expected, as well as any products which are >in more than one TopCategory. > >Any ideas how to overcome this would be greatly appreciated. > >Cheers > >Craig > Hi Craig, The first idea will have to come from you. SQL Server won't make your choices for you. So you must decide *which* TopCategory to display for products that are in more than one TopCategory. If you can describe how to choose, I (or someone else in this NG) will probably be able to help writing up the query. Best, Hugo --
[quoted text, click to view]
On Fri, 14 May 2004 09:30:00 +1200, Craig Hoskin wrote: > >"Hugo Kornelis" <hugo@pe_NO_rFact.in_SPAM_fo> wrote in message >news:7up7a0horj1pjdvu7b2qcvf23sktec53sr@4ax.com... >Hi Hugo >> >> The first idea will have to come from you. SQL Server won't make your >> choices for you. So you must decide *which* TopCategory to display for >> products that are in more than one TopCategory. > >Sorry, yes I should have calrified that aspect. Answer: I dont care :-) >Any topcategory will do as its still the same product, and the produut >display page will actually list all the categories its in. > >Thanks for taking the time to help me. > >Cheers > >Craig > >> If you can describe how to choose, I (or someone else in this NG) will >> probably be able to help writing up the query. Hi Craig, Hmmmm. Since SQL Server still won't choose for you, allow me to do it for you. The following two queries will select the "first" topcategory (I don't know the datatype, so it will either be the lowest numbered, the one that comes first in the alphabet or the oldest). 1: Using GROUP BY SELECT a.idProduct, a.description, a.descriptionLong, a.listPrice, a.price, a.smallImageUrl, a.stock, a.fileName, a.noShipCharge, MIN(c.topcategory) FROM products a, categories_products b, categories c WHERE a.active = -1 AND a.homePage = -1 AND a.idProduct = b.idProduct AND c.idcategory = b.idcategory AND a.prodType = 1 GROUP BY a.idProduct, a.description, a.descriptionLong, a.listPrice, a.price, a.smallImageUrl, a.stock, a.fileName, a.noShipCharge ORDER BY a.idProduct DESC 2: Using a subquery. SELECT a.idProduct, a.description, a.descriptionLong, a.listPrice, a.price, a.smallImageUrl, a.stock, a.fileName, a.noShipCharge, (SELECT MIN(c.topcategory) FROM categories_products b, categories c WHERE a.idProduct = b.idProduct AND c.idcategory = b.idcategory) FROM products a WHERE a.active = -1 AND a.homePage = -1 AND a.prodType = 1 ORDER BY a.idProduct DESC Both queries are untested. Test them both and check the results. Then compare the execution plan to see which is most efficient with your data. If you see little difference, pick the one you like best :-) Best, Hugo --
[quoted text, click to view]
On Fri, 14 May 2004 09:33:15 +1200, Craig Hoskin wrote: >No cancel that ... I forgot my c.topcategory in the qeruy. > >Back to seeking help again :-) > Hi Craig, So I noted. But it still was a useful post, as it showed that the active, homepage and product columns are all in the Products table (something that was not clear from your first post). Without this knowledge, I could not have presented my second option (see my other post). Best, Hugo --
[quoted text, click to view]
On Fri, 14 May 2004 10:08:16 +1200, Craig Hoskin wrote: (snip) [quoted text, click to view] >This last one looked the best. > >From a cursory glance, which would be better, yours where the SELECT is a >subquery (forgove my terminiology) or mine where it is in the WHERE clause? Hi Craig, Your query seems to contain some redundant joins. This may not hurt you if the optimizer "sees" that it doesn't need to perform the join twice, but I'm not sure if the optimizer is intelligent enough for that. [quoted text, click to view] > >Both provide the same results (it appears) and appear OK performace wise ... >just wondering which is better for a larger number of records? Without testing, I'd say mine is quicker (probably) or they have equal performance (slight chance). But you'd have to test to be sure. Make sure you have an adequate (ie large) set of test data with as typical distribution as possible. Run both queries with SET STATISTICS IO. Also, compare the execution plans. Best, Hugo --
[quoted text, click to view]
On Fri, 14 May 2004 00:48:02 +0200, Hugo Kornelis wrote: >On Fri, 14 May 2004 10:08:16 +1200, Craig Hoskin wrote: > >(snip) >>This last one looked the best. >> >>From a cursory glance, which would be better, yours where the SELECT is a >>subquery (forgove my terminiology) or mine where it is in the WHERE clause? > >Hi Craig, > >Your query seems to contain some redundant joins. This may not hurt you if >the optimizer "sees" that it doesn't need to perform the join twice, but >I'm not sure if the optimizer is intelligent enough for that. Forgot to mention - my first suggestion (identical to Erland's) has a high probability of being the fastest of them all (though it does depend on what indexes are available for the optimizer to toy with). I suspect the long group by list is what makes this one "look" less good to you. However, this will probably look better to the optimizer than the correlated subqueries in my second query and in your post. Also, do keep in mind that you can mentally replace the entire group by list by "all columns in the select clause except topcategory". Best, Hugo --
Hi everyone
Have a problem I would areally appreciate help with. I have 3 tables in a standard format for a Bookshop, eg Products Categories Categories_Products the latter allowing me to have products in multiple categories. Everthing works well except for one annoying little thing. When an individual product (which is in more than one topcategory) is added to the Shopping Cart it displays twice, because in my select statement I have the Category listed. I realise I could remove the TopCategory from the statement and that makes my DISTINCT work as I wanted, but Id prefer to have the TopCategory as it saves me later having to another SQL query (Im already doing one to allow me not to list category in the Statement .... but If I can overcome this one ... then I can remove this as well). Here is my table structure (the necessary bits) products idProduct int .... categories idcategory int idParentCategory int topcategory int ... categories_products idCatProd int idProduct int idCategory When I run a query such as SELECT DISTINCT a.idProduct, a.description,a.descriptionLong, a.listPrice,a.price,a.smallImageUrl,a.stock, a.fileName,a.noShipCharge, c.topcategory FROM products a, categories_products b, categories c WHERE active = -1 AND homePage = -1 AND a.idProduct = b.idProduct AND c.idcategory=b.idcategory AND prodType = 1 ORDER BY a.idProduct DESC This will return all products as expected, as well as any products which are in more than one TopCategory. Any ideas how to overcome this would be greatly appreciated. Cheers Craig
Doooh
Always the way, you post a question and find a possible solution :-) Would this work? select a.idProduct, a.description,a.descriptionLong, a.listPrice,a.price,a.smallImageUrl,a.stock, a.fileName,a.noShipCharge from Products a where exists (select * from categories c, categories_products b where a.idProduct = b.idProduct AND c.idcategory=b.idcategory) AND a.active = -1 AND a.homePage = -1 AND a.prodType = 1 ORDER BY a.idProduct DESC Cheers Craig [quoted text, click to view] > Hi everyone > > Have a problem I would areally appreciate help with. > I have 3 tables in a standard format for a Bookshop, eg > > Products > Categories > Categories_Products > > the latter allowing me to have products in multiple categories. > > Everthing works well except for one annoying little thing. > > When an individual product (which is in more than one topcategory) is added > to the Shopping Cart it displays twice, because in my select statement I > have the Category listed. I realise I could remove the TopCategory from the > statement and that makes my DISTINCT work as I wanted, but Id prefer to have > the TopCategory as it saves me later having to another SQL query (Im already > doing one to allow me not to list category in the Statement .... but If I > can overcome this one ... then I can remove this as well). > > Here is my table structure (the necessary bits) > products > idProduct int > .... > > categories > idcategory int > idParentCategory int > topcategory int > ... > > categories_products > idCatProd int > idProduct int > idCategory > > When I run a query such as > > SELECT DISTINCT a.idProduct, a.description,a.descriptionLong, > a.listPrice,a.price,a.smallImageUrl,a.stock, a.fileName,a.noShipCharge, > c.topcategory > FROM products a, categories_products b, categories c > WHERE active = -1 AND homePage = -1 > AND a.idProduct = b.idProduct > AND c.idcategory=b.idcategory > AND prodType = 1 ORDER BY a.idProduct DESC > > This will return all products as expected, as well as any products which are > in more than one TopCategory. > > Any ideas how to overcome this would be greatly appreciated. > > Cheers > > Craig > >
[quoted text, click to view] "Hugo Kornelis" <hugo@pe_NO_rFact.in_SPAM_fo> wrote in message news:7up7a0horj1pjdvu7b2qcvf23sktec53sr@4ax.com... Hi Hugo > > The first idea will have to come from you. SQL Server won't make your > choices for you. So you must decide *which* TopCategory to display for > products that are in more than one TopCategory. Sorry, yes I should have calrified that aspect. Answer: I dont care :-) Any topcategory will do as its still the same product, and the produut display page will actually list all the categories its in. Thanks for taking the time to help me. Cheers Craig [quoted text, click to view] > If you can describe how to choose, I (or someone else in this NG) will > probably be able to help writing up the query. > > Best, Hugo > -- > > (Remove _NO_ and _SPAM_ to get my e-mail address)
No cancel that ... I forgot my c.topcategory in the qeruy.
Back to seeking help again :-)
[quoted text, click to view]
> > > If you can describe how to choose, I (or someone else in this NG) will > > probably be able to help writing up the query. Eg max(topcategory) or something / somehow?
Hi Hugo
Ive tried this which seems to work? SELECT DISTINCT a.idProduct, a.description,a.descriptionLong, a.listPrice,a.price,a.smallImageUrl,a.stock, a.fileName,a.noShipCharge, c.topcategory FROM products a, categories_products b, categories c WHERE c.topcategory = (SELECT MAX(c.topcategory) FROM categories_products b, categories c WHERE a.idProduct = b.idProduct AND c.idcategory = b.idcategory) AND active = -1 AND homePage = -1 A ND a.idProduct = b.idProduct AND c.idcategory=b.idcategory AND prodType = 1 ORDER BY a.idProduct DESC [quoted text, click to view] "Hugo Kornelis" <hugo@pe_NO_rFact.in_SPAM_fo> wrote in message news:7up7a0horj1pjdvu7b2qcvf23sktec53sr@4ax.com... > > The first idea will have to come from you. SQL Server won't make your > choices for you. So you must decide *which* TopCategory to display for > products that are in more than one TopCategory. > > If you can describe how to choose, I (or someone else in this NG) will > probably be able to help writing up the query. > > Best, Hugo
Hi Hugo
[quoted text, click to view] > > Hmmmm. Since SQL Server still won't choose for you, allow me to do it for > you. The following two queries will select the "first" topcategory (I > don't know the datatype, so it will either be the lowest numbered, the one > that comes first in the alphabet or the oldest). Thanks for that ... it was your previous posting that gave me an idea about the MAX. Good plan :-) [quoted text, click to view] > > 2: Using a subquery. > > SELECT a.idProduct, a.description, a.descriptionLong, > a.listPrice, a.price, a.smallImageUrl, a.stock, > a.fileName, a.noShipCharge, > (SELECT MIN(c.topcategory) > FROM categories_products b, categories c > WHERE a.idProduct = b.idProduct > AND c.idcategory = b.idcategory) > FROM products a > WHERE a.active = -1 > AND a.homePage = -1 > AND a.prodType = 1 > ORDER BY a.idProduct DESC > > Both queries are untested. Test them both and check the results. Then > compare the execution plan to see which is most efficient with your data. > If you see little difference, pick the one you like best :-) This last one looked the best. From a cursory glance, which would be better, yours where the SELECT is a subquery (forgove my terminiology) or mine where it is in the WHERE clause? Both provide the same results (it appears) and appear OK performace wise ... just wondering which is better for a larger number of records? Thanks again for all your help ... truly very much appreciated. Cheers Craig
Hi Erland
Thanks for the reply. I note you and Hugo both placed the "select Max()" in the SELECT part, as opposed to one I made where I placed it in the WHERE clause. Am I correct in assuming that, given you guys and your experience, that yours will be more efficient ... since you both seem to have choosen the same approach? Cheers Craig [quoted text, click to view] "Erland Sommarskog" <sommar@algonet.se> wrote in message news:Xns94E8F3D0D213EYazorman@127.0.0.1... > Craig Hoskin (nospam@infobahn.co.nz) writes: > > Have a problem I would areally appreciate help with. > > I have 3 tables in a standard format for a Bookshop, eg > > > > Products > > Categories > > Categories_Products > > > > the latter allowing me to have products in multiple categories. > > > > Everthing works well except for one annoying little thing. > > > > When an individual product (which is in more than one topcategory) is > > added to the Shopping Cart it displays twice, because in my select > > statement I have the Category listed. I realise I could remove the > > TopCategory from the statement and that makes my DISTINCT work as I > > wanted, but Id prefer to have the TopCategory as it saves me later > > having to another SQL query (Im already doing one to allow me not to > > list category in the Statement .... but If I can overcome this one ... > > then I can remove this as well). > > You want to display a distinct list, but you only want one TopCategory. > So which one do you want? > > Well, assuming you only want the highest, you could do: > > SELECT a.idProduct, a.description,a.descriptionLong, > a.listPrice,a.price,a.smallImageUrl,a.stock, a.fileName,a.noShipCharge, > MAX(c.topcategory) > FROM products a, categories_products b, categories c > WHERE active = -1 AND homePage = -1 > AND a.idProduct = b.idProduct > AND c.idcategory=b.idcategory > AND prodType = 1 ORDER BY a.idProduct DESC > GROUP BY a.idProduct, a.description,a.descriptionLong, > a.listPrice,a.price,a.smallImageUrl,a.stock, > a.fileName,a.noShipCharge > > > > -- > Erland Sommarskog, SQL Server MVP, sommar@algonet.se > > Books Online for SQL Server SP3 at > http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp
Craig Hoskin (nospam@infobahn.co.nz) writes:
[quoted text, click to view] > I note you and Hugo both placed the "select Max()" in the SELECT part, as > opposed to one I made where I placed it in the WHERE clause. > > Am I correct in assuming that, given you guys and your experience, that > yours will be more efficient ... since you both seem to have choosen the > same approach? For this case it is probably more effecient. It was also less typing... -- Erland Sommarskog, SQL Server MVP, sommar@algonet.se Books Online for SQL Server SP3 at
Don't see what you're looking for? Try a search.
|
January 2003
June 2003
July 2003
August 2003
September 2003
October 2003
November 2003
December 2003
January 2004
February 2004
March 2004
April 2004
May 2004
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
| home · blog · groups | Questions? Comments? Contact the dn |