How do I display total records in a Manytomany relationship when I use HAVING?

Asked

Viewed 186 times

1

I have the tables below:

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

In the query below I can recover the articles that have the two 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 make a select Count for me to know the total of records returned without displaying the data itself?

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

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

But I wanted something like (total of articles that have the two tags):

| total_articles |
------------------
| 2              |
mysql sql

  • article_id is PK?

  • Yes, PK in table Articles and FK in table article_tag

2 answers

1

I imagine this is what you want, you must select the COUNT from the article_id field to return the amount 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;

  • This always returns as a result the value 2 which is the number of searched tags and also returns more than one record always with the value 2 (searched tag_id number)

  • and wouldn’t that be what Voce wants? bring the amount of records you found in select?

  • Yes, but this query does not return. It returns multiple records with the value 2 which is the amount of searched tags and not the amount of found articles.

  • try grouping by tag_id then, because this way above it groups by article_id if you have 2 bring 2 results

0

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

Put a COUNT select and remove the groupby

Browser other questions tagged mysql sql

You are not signed in. Login or sign up in order to post.