How to get number of occurrences in a SQL IN clause

Question

Let say i have four tables PAGE, USER, TAG and PAGE-TAG

PAGE has fields ID, CONTENT ...

TAG has fields ID, NAME ...

USER has fields ID, NAME ...

PAGE-TAG has fields ID, PAGE-ID, TAG-ID, USER-ID

Let say i have four pages

PAGE#1 'Content page 1' tagged with tag#1 by user1, tagged with tag#1 by user2

PAGE#2 'Content page 2' tagged with tag#3 by user2, tagged by tag#1 by user2, tagged by tag#8 by user1

PAGE#3 'Content page 3' tagged with tag#7 by user#1

PAGE#4 'Content page 4' tagged with tag#1 by user1, tagged with tag#8 by user1

I expect my query to look something like this:

select page.content ?
from page, page-tag 
where 
page.id = page-tag.pag-id 
and page-tag.tag-id in (1, 3, 8) 
order by ? desc

I would like to get such an output:

Content page 2, 3

Content page 4, 2

Content page 1, 1

thanks in advanced for the feedback.

Answer 1

This might work:

select page.content, count(page-tag.tag-id) as tagcount
from page inner join page-tag on page-tag.page-id = page.id
group by page.content
having page-tag.tag-id in (1, 3, 8)

Answer 2

Your question is a bit confusing. Do you want to get the number of times each page has been tagged? The number of times each page has gotten each tag? The number of unique users that have tagged a page? The number of unique users that have tagged each page with each tag?

Answer 3

In T-Sql:

select count(distinct name)
from page-tag
where tag-id in (1, 3, 8)

This will give you a count of the number of different tag names for your list of ids

Answer 4

Agree with Neall, bit confusing the question. If you want the output listed in the question, the sql is as simple as:

select page.content, page-tag.tag-id
from page, page-tag 
where page.id = page-tag.pag-id 
and page-tag.tag-id in (1, 3, 8) 
order by page-tag.tag-id desc

But if you want the tagcount, Daren answered your question

Answer 5

select 
    page.content, 
    count(pageTag.tagID) as tagCount
from 
    page
    inner join pageTag on page.ID = pageTag.pageID
where 
    pageTag.tagID in (1, 3, 8) 
group by
    page.content
order by
    tagCount desc

That gives you the number of tags per each page; ordered by the higher number of tags

I hope I understood your question correctly

Answer 6

Quoting Neall

Your question is a bit confusing. Do you want to get the number of times each page has been tagged?

No

The number of times each page has gotten each tag?

No

The number of unique users that have tagged a page?

No

The number of unique users that have tagged each page with each tag?

No

I want to know how many of the passed tags appear in a particular page, not just if any of the tags appear.

SQL IN works like an boolean operator OR. If a page was tagged with any value within the IN Clause then it returns true. I would like to know how many of the values inside of the IN clause return true.

Below i show, the output i expect:

page 1 | in (1,2)   -> 1

page 1 | in (1,2,3) -> 1

page 1 | in (1)     -> 1

page 1 | in (1,3,8) -> 1

page 2 | in (1,2)   -> 1

page 2 | in (1,2,3) -> 2

page 2 | in (1)     -> 1

page 2 | in (1,3,8) -> 3

page 4 | in (1,2,3) -> 1

page 4 | in (1,2,3) -> 1

page 4 | in (1)     -> 1

page 4 | in (1,3,8) -> 2

This will be the content of the page-tag table i mentioned before:

   id       page-id  tag-id  user-id  

    1       1        1       1 

    2       1        1       2 

    3       2        3       2 

    4       2        1       2 

    5       2        8       1 

    6       3        7       1 

    7       4        1       1 

    8       4        8       1

@Kristof does not exactly what i am searching for but thanks anyway.

@Daren If i execute you code i get the next error:

#1054 - Unknown column 'page-tag.tag-id' in 'having clause'

@Eduardo Molteni Your answer does not give the output in the question but:

Content page 2 8
Content page 4 8
content page 2 3
content page 1 1
content page 1 1
content page 2 1
cotnent page 4 1

@Keith I am using plain SQL not T-SQL and i am not familiar with T-SQL, so i do not know how your query translate to plain SQL.

Any more ideas?

Answer 7

OK, so the key difference between this and kristof's answer is that you only want a count of 1 to show against page 1, because it has been tagged only with one tag from the set (even though two separate users both tagged it).

I would suggest this:

SELECT page.ID, page.content, count(*) AS uniquetags FROM
    ( SELECT DISTINCT page.content, page.ID, page-tag.tag-id FROM page INNER JOIN page-tag ON page.ID=page-tag.page-ID WHERE page-tag.tag-id IN (1, 3, 8) )
    GROUP BY page.ID

I don't have a SQL Server installation to check this, so apologies if there's a syntax mistake. But semantically I think this is what you need.

This may not give the output in descending order of number of tags, but try adding:

ORDER BY uniquetags DESC

at the end. My uncertainty is whether you can use ORDER BY outside of grouping in SQL Server. If not, then you may need to nest the whole thing in another SELECT.

Answer 8

Leigh Caldwell answer is correct, thanks man, but need to add an alias at least in MySQL. So the query will look like:

SELECT page.ID, page.content, count(*) AS uniquetags FROM
    ( SELECT DISTINCT page.content, page.ID, page-tag.tag-id FROM page INNER JOIN page-tag ON page.ID=page-tag.page-ID WHERE page-tag.tag-id IN (1, 3, 8) ) as page
    GROUP BY page.ID
order by uniquetags desc