February 7, 2009 at 9:12 am
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 partneraantal
FROM product LEFT JOIN partner ON product.id = partner.productid LEFT JOIN shop ON partner.shopnaam = shop.shopshopnaam
WHERE product.categorie = 'XXX' AND product.hoofdmodel = '1'
GROUP BY product.naam
ORDER BY product.naam ASC, shop.shopcpc DESC
Everything 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.
February 7, 2009 at 10:35 am
What do you mean by 'wrong row joined'?
Order by is evaluated way after the joins are done, it's the last clause that gets evaluated.
Can you post table structure, some sample data, your expected results and your actual results please.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 7, 2009 at 11:19 am
When you say that the ORDER BY statement doesn't work, are you stating that it is throwing up an error and that you are not able to execute the query, or are you saying that the output of the results are not correct?
Please remember that an ORDER BY clause will sort by the first column, and then the second column will be sorted within the results of the 1st column.
So if you have
Product / Shop
1/1
1/2
1/3
2/4
2/1
you would get results like this from the above query:
product shop
1 3
1 2
1 1
2 4
2 1
Product is sorted first in ascending order. Then shop is order in decending order within the product it is with.
Fraggle
February 7, 2009 at 11:24 am
Fraggle (2/7/2009)
When you say that the ORDER BY statement doesn't work, are you stating that it is throwing up an error and that you are not able to execute the query, or are you saying that the output of the results are not correct?Please remember that an ORDER BY clause will sort by the first column, and then the second column will be sorted within the results of the 1st column.
So if you have
Product / Shop
1/1
1/2
1/3
2/4
2/1
you would get results like this from the above query:
product shop
1 3
1 2
1 1
2 4
2 1
Product is sorted first in ascending order. Then shop is order in decending order within the product it is with.
Fraggle
Hi,
Sorry, could have been a little bt more precise.
First: everything is outputted and I do not get an error message.
I understand that everything is ordered first with product.naam, but it isnt ordered secondly by shop.shopcpc. So, even if I change shop.shopcpc to ASC, it would still output the same.
Sample data (example):
product.id
1000
row --- partner.productid --- partner.shopnaam
1 --- 1000 --- shop A
2 --- 1000 --- shop B
3 --- 1000 --- shop C
4 --- 1000 --- shop D
row --- shop.shopshopnaam --- shop.shopcpc
1 --- shop A --- 10
2 --- shop C --- 20
3 --- shop B --- 40
4 --- shop D --- 5
Here the output should be:
product.id = 1000
partner.shopnaam = shop b
So, 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)
February 7, 2009 at 11:40 am
From what I understand, you are actually wanting to sort, not by shop, but by Partneraantal. Is this correct? If so, then the issues is the ORDER BY clause as you though, but not for the reason you thought.
ORDER BY Product.naam ASC, shop.shopcpc DESC
should actually be
ORDER BY Product.naam ASC, Partneraantal DESC
This will order the results by product, then the count in des order, giving you the Shop B first in line with 40.
Fraggle
February 7, 2009 at 11:51 am
Fraggle (2/7/2009)
From what I understand, you are actually wanting to sort, not by shop, but by Partneraantal. Is this correct? If so, then the issues is the ORDER BY clause as you though, but not for the reason you thought.ORDER BY Product.naam ASC, shop.shopcpc DESC
should actually be
ORDER BY Product.naam ASC, Partneraantal DESC
This will order the results by product, then the count in des order, giving you the Shop B first in line with 40.
Fraggle
Hi Fraggle,
Not exactly, I'll give you an example page to make it a little bit more clear: http://www.kinderwagensuXXly.nl/Voorbeeld /Kinderwagens/ (replace te two XX with "pp" (dont want this to get indexed).
Inlog: Helpmij
Password: Helpmij
Here you see a lot of products (which should only be shown once) from the product table. The green button has an URL and information from the partner table. I want te URL (partner.url) to show up where (partner.shopnaam = shop.shopnaam)shop.shopcpc is highest.
Also, the count shows the total number of shops found that have his product. See: "Maxi Cosi Mura 3
Voorradig bij 4 winkels"
To be even more clear: when you hover over the green button of the "Maxi Cosi Mura 3" product you see that it matches the shop "Aktiebaby". Aktiebaby's shop.shopcpc isnt highest of the 4 shops and even if I order shop.shopcpc ASC it will still show the partner-product row of Aktiebaby...
February 7, 2009 at 12:04 pm
Alright, I do not have a complete understanding, but I am getting there.
you want to return a result set of
product 1 shop b count 40
product 2 shop a count 20
where the count is the shop with the highest count of the product correct?
Fraggle
February 7, 2009 at 12:16 pm
Fraggle (2/7/2009)
Alright, I do not have a complete understanding, but I am getting there.you want to return a result set of
product 1 shop b count 40
product 2 shop a count 20
where the count is the shop with the highest count of the product correct?
Fraggle
Hi Fraggle,
NO problem, I can understand its quite complex to get the idea i'm getting at. I want to return the result of a product (e.g. product.id =1000) with the partner.shopnaam (name of the shop where people can buy it)) where the shop.shopcpc (this is the amount of money I receive per click) is highest and where the entire list is ordered alphabetically on product.naam.
The COUNT is added to give the visitor information on the number of shops the product can be purchased at (so the number of connections between product.id and partner.productid)
My own product information is stored in "product"
Infomation on all products of all partners like; productURL, the partner's productid, partners product imageURL etc etc are stored in "partner"
The shop table contains information per shop and its average "per click revenue" (shop.shopcpc).
So; as you can see on the example page: all information you can see are unique product rows (product.id's) where the information in the green button has to be the matching partner rows (product.id=partner.productid) where only the one with the highest shop.shopcpc is matched (partner.shopnaam = shop.shopshopnaam). In the green line just below the product name we see the COUNT which gives the visitor information on the total number of partners selling the product which they all can see when clicking on the product name (doesnt work right now)
If this isnt clear, please let me know.
February 7, 2009 at 12:47 pm
So really we are looking at
Product 1 Shop b Count = 40
where shop b has the highest amount of money made for you per click, not the highest count of product. Am I now understanding this correctly?
Fraggle
February 7, 2009 at 1:15 pm
Fraggle (2/7/2009)
So really we are looking atProduct 1 Shop b Count = 40
where shop b has the highest amount of money made for you per click, not the highest count of product. Am I now understanding this correctly?
Fraggle
I think you do fraggle 🙂 .
So now looking back at the query:
SELECT *, COUNT(partner.productid) AS partneraantal
FROM product LEFT JOIN partner ON product.id = partner.productid LEFT JOIN shop ON partner.shopnaam = shop.shopshopnaam
WHERE product.categorie = 'XXX' AND product.hoofdmodel = '1'
GROUP BY product.naam
ORDER BY product.naam ASC, shop.shopcpc DESC
looks right to me, but the last part doesnt seem to work...(Everything else including count does work as you can see on the example page)
February 7, 2009 at 1:24 pm
Well the good news is that the ORDER BY isn't the issue. The bad news is that the query is the issue.
Is should look like something similar to this, although, without know the exact table structure, I cannot verify results.
SELECT *, COUNT(partner.productid) AS partneraantal
FROM product
LEFT JOIN partner ON product.id = partner.productid
Inner join (select shopnaam, max(clickprice) as MaxClickPrice
from shop
group by shopnaam
) as Tmp on Partner.shopnaam = tmp.shopnaam
WHERE product.categorie = 'XXX' AND product.hoofdmodel = '1'
GROUP BY product.naam, tmp.maxclickprice
ORDER BY product.naam ASC, shop.shopcpc DESC
[\code]
The subquery will return a shopname and it's max price per click. This should give you the results you are looking for, or something close to it, so a couple of tweeks will get you there.
Fraggle.
February 7, 2009 at 2:01 pm
Please can you post sample data and expected results here. I'm not fully understanding what you want, I think it's simple, but am not sure. We could be asking for clarification for days and still not understanding exactly what you want.
Please read this article. http://www.sqlservercentral.com/articles/Best+Practices/61537/
If I'm reading correctly, you're expecting the order by to do some filtering? It won't it's strictly going to order what rows are in the resultset, not remove rows.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 7, 2009 at 4:21 pm
Hi Fraggle,
SELECT *, COUNT(partner.productid) AS partneraantal
FROM product LEFT JOIN partner ON product.id = partner.productid Inner join (SELECT shop.shopshopnaam, max(shop.shopcpc) as MaxClickPrice FROM shop
GROUP BY shop.shopshopnaam) as Tmp on partner.shopnaam = Tmp.shopshopnaam WHERE product.categorie = 'Kinderwagens' AND product.hoofdmodel = '1' GROUP BY product.naam, Tmp.maxclickprice
ORDER BY product.naam ASC, MaxClickPrice DESC
didn't work how I hoped it would. Now, only product.id products with an actual match to partner.productid are outptutted. Also, when there is a match between product.id and partner.productid, every match is outputted seperately (once for every match with partner.productid). The COUNT now also stays on 1 all the. So, if product.id = 1000 and 4 matches are foud on partner.productid it outputs product.id 4 times (once for every match with partner.productid) and counts only one match.
@ Gilamonster; here a good example with example data.
Product.id --- product.naam
1000 --- A product
2000 --- C product
3000 --- E product
4000 --- D product
5000 --- G product
6000 --- F product
partner.productid --- partner.shopnaam
1000 --- Shop A
1000 --- Shop C
1000 --- Shop D
3000 --- Shop C
3000 --- Shop D
4000 --- Shop A
4000 --- Shop D
5000 --- Shop B
5000 --- Shop D
6000 --- Shop A
shop.shopshopnaam --- shop.shopcpc
Shop A --- 5
Shop B --- 2
shop C --- 7
shop D --- 4
Rows to be outputted with sql code is all information on the following table rows:
product.naam / product.id --- partner.productid / partner.shopnaam--- shop.shopshopnaam / shop.shopcpc
A product / 1000 --- 1000 / shop C --- shop C / 7
C product / 2000 --- - --- -
D product / 4000 --- 4000 / shop A --- shop A / 5
E product / 3000 --- 3000 / shop C --- shop C / 7
F product / 6000 --- 6000 / shop A --- shop A / 5
G product / 5000 --- 5000 / shop D --- shop D / 2
Besides these variables also other colums on the table same rows should be accessible like product.categorie, partner.url (url to the corresponding external partner product), etc (thats why I tried to do this with SELECT *)
Hope this makes things clear
Viewing 13 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply