CODEWITHSUNDEEP
mysqlcounttagsmatch
Amy Neville
Amy Neville

Reputation: 10611

Counting matches in mysql

I need to make a query that will match blogs that have matching tag_id in table tags_blogs. There is another table that contains the actual tags which I am not concerned about at this stage.

How do I take this table contents:

reference_id    tag_id  blog_id
1               1       1   
2               2       1   
3               10      6   
4               11      6   
5               10      7   
6               11      7
7               11      8

And return this where (for example) blog_id = 6:

blog_id  total_matches
7           2
8           1

In other words return any blog's ids that have matching tag_id to the parameter provided, as well as a count of how many matches were achieved.

This is the code I have so far (I am way off so far):

SELECT blog_id FROM tags_blogs WHERE blog_id = 6

Upvotes: 0

Views: 4860

Answers (2)

Patrick Evans
Patrick Evans

Reputation: 42736

You need a subquery to select all of 6 (or whatever blog id) tags and if a blog has a tag id IN that subquery it gets selected, then groups same blog_ids together and counts them.

SELECT 
     a.blog_id,
     count(*) as total_matches
FROM 
     tags_blogs as a
WHERE 
     a.tag_id IN
     ( SELECT tag_id FROM tags_blogs WHERE b.blog_id=6 ) AND
     a.blog_id!=6 
GROUP BY a.blog_id

will return results like

blog_id   total_matches
7         2
8         2

Upvotes: 2

Brian Hoover
Brian Hoover

Reputation: 7991

From your comments, here's more like what you are looking for. Note that this particular query isn't necessarily optimal

select tb.blog_id, count(*) 
  as total_matches 
  from tags_blogs tb
  where tag_id in (select distinct tag_id from tags_blogs where blog_id = 6)
    and blog_id != 6
  group by blog_id

Link to SQL Fiddle

You might find that this is a little more efficient in some circumstances, or easier to create:

select tb.blog_id, count(*) 
  as total_matches 
  from tags_blogs tb
    join tags_blogs tb1 on tb1.tag_id = tb.tag_id and tb1.blog_id != tb.blog_id
    where tb1.blog_id = 6
  group by tb.blog_id;

Upvotes: 1

Related Questions