SQL Query for Product-Tag relationship

Question

Hi

I have a database for an E-commerce storefront. MSSQL 2008.

I have a table called Products and a table called Tags. This is a many to many relationship that is bound together by a table called ProductTags.

Products:
id, name, price

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

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 to this Tag. ChildTagCount is the number of Tags that has this Tag's id as its parentid.

Answer 1

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

would that work?

Answer 2

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

Answer 3

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

To be honest. The view I created myself was exactly the same as devio's - only I forgot the DISTINCT in the two COUNTs - which saw the values explode!