Forum Moderators: open
To output data on my .php website I use a SQL database. One particular page needs information from 3 tables:
1. Product (product.id, product.name
2. Partner (partner.productid, parner.shopname)
3. Shop (shop.shopshopname)
I currently have this code running to make it happend:
SELECT *
FROM (product LEFT JOIN partner ON product.id = partner.productid) LEFT JOIN shop ON partner.shopnaam = shop.shopshopnaam
WHERE product.categorie = 'Kinderwagens' AND product.hoofdmodel = '1'
ORDER BY product.name ASC, shop.shopcpc DESC
The idea is that every product.name gets outputted in a list just once and that it picks partner.productid of the row with the highest corresponding shop.cpc.
Example: I have a lot of product.id's where product.id = "1000 is found 4 times in partner.productid. All product.name's are outputted and ascend correctly. However, product.name with product.id=1000 is outputted 4 times. I only want it outputted once where it pickes the corresponding partner.id where its corresponding shop.shopcpc is highest. What do I do to prevent it from outputting all 4?
I know its kind of complex to explain, if there are questions please let me know.
SELECT * --> But product.name = DISTINCT
Does such an abbreviation exist?
SELECT *
FROM (product LEFT JOIN partner ON product.id = partner.productid) LEFT JOIN shop ON partner.shopname = shop.shopshopname
WHERE product.categorie = '#*$!X' AND product.hoofdmodel = '1'
GROUP BY product.name
ORDER BY product.name ASC, shop.shopcpc DESC
I still want to add one feature: I want to implement "COUNT partner.productid AS partneramount". So, even though I only want to output one of the left joins between product.id and partner.productid, I still want to output the total nmer of matches (that could be) found!
EDIT: However, by adding GROUP BY my ORDER BY shop.shopcpc DESC doesnt work anymore. Anyone know how to fix this?
[edited by: Joppiesaus at 12:05 pm (utc) on Feb. 6, 2009]
