当前位置: 动力学知识库 > 问答 > 编程问答 >

sql - Mysql multi join with count

问题描述:

have 3 tables

product_tags

product_id | tag

___________________

50 | new

50 | blac

66 | new

50 | green

111 | new

111 | white

products_to_categories

product_id | category_id

____________________

50 | 69

50 | 68

111 | 40

111 | 70

categories

category_id | parent_id (parent category id)

____________________

68 | 0

69 | 68

70 | 68

need all tags sorted by popularity (count product) within category 68 and its all subcategories (all categories with parent id 68)

my start query give wrong result

SELECT tag

FROM product_tags opd

LEFT JOIN products_to_categories optc ON optc.product_id = opd.product_id

LEFT JOIN categories optx ON optx.parent_id = '68'

WHERE opd.tag <> ''

AND optx.parent_id = '68'

ORDER BY optc.product_id DESC

Result I need

tags

_____

new (2)

white (1)

网友答案:

Do this step by step. Use EXISTS or IN when checking whether a record exists. You want product_ids that are in the set of category_ids 68 and its children:

select tag, count(*)
from product_tags
where product_id in
(
  select product_id
  from products_to_categories
  where category_id = 68
  or category_id in
  (
    select category_id
    from categories
    where parent_id = 68
  )
)
group by tag
order by count(*) desc;
网友答案:

First, your Join on categories was incorrect. It should be:

 LEFT JOIN categories optx ON optx.parent_id = optc.category_id 

Then to get the correct count() you should do a GROUP BY tag:

SELECT CONCAT(opd.tag, ' (', count(*), ')' )
FROM product_tags opd 
  LEFT JOIN products_to_categories optc ON optc.product_id = opd.product_id 
  LEFT JOIN categories optx ON optx.parent_id = optc.category_id 
WHERE opd.tag <> '' 
  AND optx.parent_id = '68' 
GROUP BY opd.tag
网友答案:

I think your biggest problem is that you are getting confused with your data and how it pieces together. I have rewritten your query removing your aliases so that you can clearly see what is happening with your joins.

SELECT tag, COUNT(*) AS Num
FROM product_tags
    LEFT JOIN products_to_categories ON product_tags.product_id = product_to_categories.product_id 
    LEFT JOIN categories ON product_to_categories.category_id  = categories.category_id
WHERE product_tags.tag <> '' AND categories.parent_id = '68'
GROUP BY tag
ORDER BY Num DESC 

I would then use your presentation layer to handle the presentation of your data "New (2)" etc.

Hope this helps.

网友答案:

I think you have a problem with your tables, because you can't do the difference between products id 50 and 111, so, I advise you to move the category_id in product_tags table.

分享给朋友:
您可能感兴趣的文章:
随机阅读: