<p>Let's say I have four tables: <code>PAGE</code>, <code>USER</code>, <code>TAG</code>, and <code>PAGE-TAG</code>:</p>

<pre><code>Table      | Fields
------------------------------------------
PAGE       | ID, CONTENT
TAG        | ID, NAME
USER       | ID, NAME    
PAGE-TAG   | ID, PAGE-ID, TAG-ID, USER-ID
</code></pre>

<p>And let's say I have four pages:</p>

<pre><code>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
</code></pre>

<p>I expect my query to look something like this: </p>

<pre><code>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
</code></pre>

<p>I would like to get output like this:</p>

<pre><code>Content page 2, 3
Content page 4, 2
Content page 1, 1
</code></pre>

<hr>

<p>Quoting Neall </p>

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

<p>No</p>

<blockquote>
  <p>The number of times each page has gotten each tag? </p>
</blockquote>

<p>No</p>

<blockquote>
  <p>The number of unique users that have tagged a page? </p>
</blockquote>

<p>No </p>

<blockquote>
  <p>The number of unique users that have tagged each page with each tag?</p>
</blockquote>

<p>No</p>

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

<p>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. </p>

<p>Below i show, the output i expect: </p>

<pre><code>page 1 | in (1,2)   -&gt; 1

page 1 | in (1,2,3) -&gt; 1

page 1 | in (1)     -&gt; 1

page 1 | in (1,3,8) -&gt; 1

page 2 | in (1,2)   -&gt; 1

page 2 | in (1,2,3) -&gt; 2

page 2 | in (1)     -&gt; 1

page 2 | in (1,3,8) -&gt; 3

page 4 | in (1,2,3) -&gt; 1

page 4 | in (1,2,3) -&gt; 1

page 4 | in (1)     -&gt; 1

page 4 | in (1,3,8) -&gt; 2
</code></pre>

<p>This will be the content of the page-tag table i mentioned before: </p>

<pre><code>   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 
</code></pre>

<p><strong>@Kristof</strong> does not exactly what i am searching for but thanks anyway. </p>

<p><strong>@Daren</strong> If i execute you code i get the next error: </p>

<pre><code>#1054 - Unknown column 'page-tag.tag-id' in 'having clause' 
</code></pre>

<p><strong>@Eduardo Molteni</strong> Your answer does not give the output in the question but: </p>

<pre><code>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
</code></pre>

<p><strong>@Keith</strong> 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.</p>

<p>Any more ideas?</p>

[{'answer_id': 32070, 'author': 'Daren Thomas', 'author_id': 2260, 'author_profile': 'https://Stackoverflow.com/users/2260', 'pm_score': 2, 'selected': False, 'text': '<p>This might work:</p>\n\n<pre><code>select page.content, count(page-tag.tag-id) as tagcount\nfrom page inner join page-tag on page-tag.page-id = page.id\ngroup by page.content\nhaving page-tag.tag-id in (1, 3, 8)\n</code></pre>\n'}, {'answer_id': 32088, 'author': 'Keith', 'author_id': 905, 'author_profile': 'https://Stackoverflow.com/users/905', 'pm_score': 0, 'selected': False, 'text': '<p>In T-Sql:</p>\n\n<pre><code>select count(distinct name)\nfrom page-tag\nwhere tag-id in (1, 3, 8) \n</code></pre>\n\n<p>This will give you a count of the number of different tag names for your list of ids</p>\n'}, {'answer_id': 32095, 'author': 'Eduardo Molteni', 'author_id': 2385, 'author_profile': 'https://Stackoverflow.com/users/2385', 'pm_score': 0, 'selected': False, 'text': '<p>Agree with <strong>Neall</strong>, bit confusing the question.\nIf you want the output listed in the question, the sql is as simple as:</p>\n\n<pre><code>select page.content, page-tag.tag-id\nfrom page, page-tag \nwhere page.id = page-tag.pag-id \nand page-tag.tag-id in (1, 3, 8) \norder by page-tag.tag-id desc\n</code></pre>\n\n<p>But if you want the tagcount, <strong>Daren</strong> answered your question </p>\n'}, {'answer_id': 32159, 'author': 'kristof', 'author_id': 3241, 'author_profile': 'https://Stackoverflow.com/users/3241', 'pm_score': 0, 'selected': False, 'text': '<pre><code>select \n    page.content, \n    count(pageTag.tagID) as tagCount\nfrom \n    page\n    inner join pageTag on page.ID = pageTag.pageID\nwhere \n    pageTag.tagID in (1, 3, 8) \ngroup by\n    page.content\norder by\n    tagCount desc\n</code></pre>\n\n<p>That gives you the number of tags per each page; ordered by the higher number of tags</p>\n\n<p>I hope I understood your question correctly</p>\n'}, {'answer_id': 32560, 'author': 'Leigh Caldwell', 'author_id': 3267, 'author_profile': 'https://Stackoverflow.com/users/3267', 'pm_score': 2, 'selected': True, 'text': "<p>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).</p>\n\n<p>I would suggest this:</p>\n\n<pre><code>SELECT page.ID, page.content, count(*) AS uniquetags\nFROM\n   (  SELECT DISTINCT page.content, page.ID, page-tag.tag-id \n      FROM page INNER JOIN page-tag ON page.ID=page-tag.page-ID \n      WHERE page-tag.tag-id IN (1, 3, 8) \n   )\n    GROUP BY page.ID\n</code></pre>\n\n<p>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.</p>\n\n<p>This may not give the output in descending order of number of tags, but try adding:</p>\n\n<pre><code>      ORDER BY uniquetags DESC\n</code></pre>\n\n<p>at the end. My uncertainty is whether you can use <code>ORDER BY</code> outside of grouping in SQL Server. If not, then you may need to nest the whole thing in another <code>SELECT</code>.</p>\n"}, {'answer_id': 32655, 'author': 'Sergio del Amo', 'author_id': 2138, 'author_profile': 'https://Stackoverflow.com/users/2138', 'pm_score': 0, 'selected': False, 'text': '<p>Leigh Caldwell answer is correct, thanks man, but need to add an alias at least in MySQL. So the query will look like: </p>\n\n<pre><code>SELECT page.ID, page.content, count(*) AS uniquetags FROM\n    ( 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\n    GROUP BY page.ID\norder by uniquetags desc\n</code></pre>\n'}]
['https://Stackoverflow.com/questions/32059', 'https://Stackoverflow.com', 'https://Stackoverflow.com/users/2138/']