Query for product count with matching tags per tag-CodePudding

I have a products table and each product can have multiple tags and the products can be filtered by these tags.

I am trying to get a list of tags based on the active filter.

products

 ---- ----------- 
| id |   name    |
 ---- ----------- 
|  1 | Product 1 |
|  2 | Product 2 |
|  3 | Product 3 |
 ---- -----------

tags

 ---- ----------- 
| id |   name    |
 ---- ----------- 
|  1 | Tag1      |
|  2 | Tag 2     |
|  3 | Tag 3     |
 ---- -----------

productTags

 ----------- ------- 
| productId | tagId |
 ----------- ------- 
|         1 |     1 |
|         1 |     2 |
|         1 |     3 |
|         2 |     1 |
|         2 |     2 |
|         3 |     1 |
 ----------- -------

The result table of tags i would like to get looks like this:

id	name	productCount
1	Tag1	3
2	Tag2	0
3	Tag3	1

CodePudding user response：

So I came up with this query which seems to work fine.

It get's the tags based on the tag filters. If the products are filtered by Tag1 & Tag2 I get all the tags and the count of how many products will remain if I filter by each tag.

SELECT
t.id,
t.name,
(
    SELECT COUNT(*) from (
        SELECT
        pt.productId
        FROM productTags pt

        INNER JOIN productTags pt2
            ON pt2.productId = pt.productId
            AND pt2.tagId IN (1,2)

        WHERE pt.tagId = t.id

        GROUP BY pt.productId

        HAVING COUNT(DISTINCT pt2.tagId) = 2

    ) AS count

) AS count

FROM tags t

CodePudding user response：

Try this:

SELECT t.id, t.Name, COUNT(DISTINCT pt.productId) as productCount
FROM Tags t
LEFT JOIN ProductTags pt on pt.tagId = t.id
GROUP BY t.id, t.Name

Or with a filter:

SELECT t.id, t.Name, COUNT(DISTINCT pt.productId) as productCount
FROM Tags t
LEFT JOIN ProductTags pt on pt.tagId = t.id
WHERE t.id IN (1,2)
GROUP BY t.id, t.Name

Or with a filter, but still show all tags:

SELECT t.id, t.Name, COALESCE(pc.productCount,0) as productCount
FROM Tags t
LEFT JOIN (
    SELECT pt.tagId, COUNT(DISTINCT pt.productId) as productCount
    FROM ProductTags pt 
    WHERE pt.tagId IN (1,2)
    GROUP BY pt.tagId
) pc ON t.id = pc.tagId

CodePudding user response：

you can use this query

SET ANSI_WARNINGS OFF;


CREATE TABLE tag (
    id int identity(1,1),
    tagname varchar(100),
  

)


    CREATE TABLE product (
    id int identity(1,1),
    productName varchar(100),
  

)

CREATE TABLE pt (
    pid int null,
    tid int null,
  

)

       insert product
       values('A')
       
       insert product
       values('B')
    
       insert tag
       values('Aa')
                   
       insert tag
       values('Bb')

insert pt
       values(1,1)
insert pt
       values(1,1)

select tag.tagname,
count(pt.pid)productcount
from tag
left join
pt on pt.tid=tag.id
group by 
tag.tagname

so result is:

if has error after SET ANSI_WARNINGS OFF; use Go

and for show row as id you can use this query

select 
 ROW_NUMBER() OVER(ORDER BY tag.tagname) AS id  
,
tag.tagname,
count(pt.pid)productcount

from tag
left join
pt on pt.tid=tag.id
group by 
tag.tagname

so result is