How to display total records in a ManyToMany relationship when I use HAVING?

Navigation
1

I have the tables below:

  • articles
  • tags
  • article_tag (pivot table with article_id and tag_id columns)

In the query below I can retrieve articles that have both tags (with id 1 and 3). In the query only articles that have the two tags that will have their ids shown. 

select article_id
from article_tag
where tag_id in (1, 3)
group by article_id
having count(distinct tag_id) = 2;

How to do a select count so I know the total of returned records without displaying the data itself?

Is that in the above query I have the listing for example: 

| article_id |
--------------
| 289        |
| 341        |

But I wanted something like (total articles that have both tags): 

| total_articles |
------------------
| 2              |
    
asked by anonymous 07.01.2016 / 04:04

2 answers

1

I imagine this is what you want, you should select the COUNT of the article_id field to return the number of records. 

select COUNT(article_id)
from article_tag
where tag_id in (1, 3)
group by article_id
having count(distinct tag_id) = 2;
    
07.01.2016 / 11:18
0
select COUNT(article_id)
from article_tag
where tag_id in (1, 3)
having count(distinct tag_id) = 2;

Put a COUNT in the select and remove the groupby

    
07.01.2016 / 13:05
Increase the menubutton box callback afterFind