Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Joppiesaus
Starting Member
2 Posts |
 Posted - 2009-02-07 : 11:07:03
|
| Hello,I posted this problem on different other forum's, but there nobody seemed to be able to figure this one out. I have this SQL query:SELECT *, COUNT(partner.productid) AS partneraantalFROM product LEFT JOIN partner ON product.id = partner.productid LEFT JOIN shop ON partner.shopnaam = shop.shopshopnaamWHERE product.categorie = 'XXX' AND product.hoofdmodel = '1'GROUP BY product.naamORDER BY product.naam ASC, shop.shopcpc DESCEverything seems to work, it outputs a list with unique produc't rows, the COUNT works properly and information is retrieved from "product", "partner" and "shop". However, the last ORDER BY statement doesn't work. So, the wrong partner and shop row are left joined to product.id (its a row where its corresponding shop.shopcpc doesnt have the highest cpc.If something is unclear or you need more information, please let me know. |
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2009-02-07 : 11:50:10
|
| Do you have sample data and output? |
 |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-02-07 : 11:57:44
|
quote:
Originally posted by Joppiesaus
Hello,I posted this problem on different other forum's, but there nobody seemed to be able to figure this one out. I have this SQL query:SELECT *, COUNT(partner.productid) AS partneraantalFROM product LEFT JOIN partner ON product.id = partner.productid LEFT JOIN shop ON partner.shopnaam = shop.shopshopnaamWHERE product.categorie = 'XXX' AND product.hoofdmodel = '1'GROUP BY product.naamORDER BY product.naam ASC, shop.shopcpc DESCEverything seems to work, it outputs a list with unique produc't rows, the COUNT works properly and information is retrieved from "product", "partner" and "shop". However, the last ORDER BY statement doesn't work. So, the wrong partner and shop row are left joined to product.id (its a row where its corresponding shop.shopcpc doesnt have the highest cpc.If something is unclear or you need more information, please let me know.
sorry your posted query is not syntactically correct. you cant use COUNT() with * in SELECT. you can only use columns given in GROUP BY in select directlyalso it would be better if you could explain problem with some sample data |
|
|
|
Joppiesaus
Starting Member
2 Posts |
Posted - 2009-02-07 : 12:11:53
|
| Hi,Thanks for your responds, how should I write the syntax then (sorry for asking so blunt, but I learned by doing a lot lately and dont have an IT background). should I write out all the necesarry kolom names like so:SELECT product.id, product.name, product.categorie, partner.*, shop.* Because even if it is faulty, the count does seem to work and the ORDER BY shop.shopcpc doesnt (with or without this count statement).Sample data (example):product.id1000row --- partner.productid --- partner.shopnaam1 --- 1000 --- shop A2 --- 1000 --- shop B3 --- 1000 --- shop C4 --- 1000 --- shop Drow --- shop.shopshopnaam --- shop.shopcpc1 --- shop A --- 102 --- shop C --- 203 --- shop B --- 404 --- shop D --- 5Here the output should be:product.id = 1000partner.shopnaam = shop bSo, the product.naam should only be outputted once with the corresponding partner.shopnaam (shop b in this case because it has the highest shop.shopcpc)Thanks. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-02-07 : 13:51:34
|
| [code]SELECT t1.productid,t2.shopshopnaam ,t2.shopcpcINTO #TempFROM partner t1INNER JOIN shop t2ON t2.shopshopnaam=t1.shopnaamSELECT t1.productid,t1.shopshopnaam ,t1.shopcpcFROM #Temp t1INNER JOIN (SELECT productid,MAX(shopcpc) AS Highest FROM #Temp GROUP BY productid)t2ON t2.productid=t1.productidAND t2.Highest=t1.shopcpc[/code] |
|
|
|
|
|
|
|
|
"Complex" SQL's ORDER BY doesn't work