Clever Geek Handbook

Relationship of SQL queries to product tag

I have a database for an e-commerce store. MSSQL 2008.

I have a table called Products and a table called Tags. This is a many-to-many relationship linked together by the ProductTags table.

Products:
id, name, price

Tags:
id, name, sortorder, parentid (allow null)

ProductTags:
productid, tagid

I'm trying to create a view in SQL, but I just completely suck at writing SQL.

The view should consist of:
Tags.id, Tags.Name, Tags.sortorder, Tags .parentid, ProductCount, ChildTagCount

ProductCount is the number of products associated with this tag. ChildTagCount is the number of tags for which this tag ID is its parent.

+1


source to share


3 answers 


SELECT Tags.ID, Tags.Name, Tags.SortOrder, Tags.ParentID,
       COUNT(DISTINCT ProductTags.ProductID) AS ProductCount, 
       COUNT(DISTINCT ChildTags.ID) AS ChildTagCount
FROM Tags
LEFT OUTER JOIN ProductTags ON Tags.ID = ProductTags.TagID
LEFT OUTER JOIN Tags ChildTags ON Tags.ID = ChildTags.ParentID
GROUP BY Tags.ID, Tags.Name, Tags.SortOrder, Tags.ParentID


+2


source 


Select T.id, T.Name, T.sortorder, T.parentid, 
(select count(*) from productstags where tagid=T.TagId) as ProductCount,
(select count(*) from Tags where parentid=T.TagId) as ChildTagCount
from Tags T


will work?

+1


source


Both suggestions work. Is there any benifit / pain effect in any of them?

Honestly. The view I created was exactly the same as devio - only I forgot the DISTINCT in the two COUNTs that saw the values ​​explode!

0


source






More articles:


All Articles