Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

There are three tables: product_hits, products, and product_subcategory_map. I want to count the number of product_hits and for each product. There could be more than one subcategory in the product_subcategory_map. I tried using a subquery to get any one of the subcategories for each product:

SELECT COUNT( product_hits.productid ) AS total, products.productid  
FROM products,
    (
        SELECT * 
        FROM product_subcategory_map 
        GROUP BY productid
    ) AS product_subcategory_map,
    product_hits, 
    maincategories  
WHERE products.productid = product_subcategory_map.productid 
AND product_hits.productid = products.productid 
AND maincategories.subcategoryid = product_subcategory_map.subcategoryid 
AND maincategories.maincategoryid = 1
GROUP BY products.productid 
ORDER BY total DESC 
LIMIT 3

Is there any way other than subquery to get only one subcategory in the table joining and so that I can calculate the correct number of product_hits?

share|improve this question
    
(SELECT * FROM product_subcategory_map GROUP BY productid) is really something you should not be doing. Because the query is choosing (semi-randomly) one subcategory for each product and then counting the hits for the product only if this subcategory passes a certain condition (maincategoryid = 1). Is that what you really want? –  ypercube Feb 6 at 10:45
    
Yes, I mean to select one subcategory randomly for each product. The hits is in the table product_hits and I just want to get one subcategory so that I can get the maincategory for those top hits products. In my case, I have four maincategories in my page, and I want to show the top hits products in these four maincategories. Each maincategories have many subcategories. I understand it is not a good practice, but I have no idea how can I improve the query. –  user33892 Feb 7 at 2:09

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.