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.
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
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?
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!