Duplicate result when using mysql join query

Question

I have 3 tables as follows
1) Tag_master

  id        tag_name
------------------------------
  1         Movies
  2         English Movies

2) Tags

   Tag_id          article
----------------------------------
     1               ABC
     2               ABC
     1               XYZ
     1               PQR
     2               EFG
     1               EFG
     2               LMN
     2               HIJ 
 --------------------------------

3)article_view

     article          view_date
  --------------------------------
       ABC            2015-07-01
       ABC            2015-07-01
       ABC            2015-07-02
       ABC            2015-07-03
       XYZ            2015-07-05
       XYZ            2015-07-05
       XYZ            2015-07-08
       PQR            2015-08-01
       PQR            2015-08-01
       LMN            2015-08-02
       HIJ            2015-08-02
 -----------------------------------

I want to get no of viewers for each article those 'Tag' containing word Movies

My query as follow

 select t.article,count(*) as cnt from tags t LEFT JOIN tag_master tm ON  
  t.tag_id=tm.id INNER JOIN article_view act ON t.article=act.article  
 WHERE tm.tag_name LIKE '%movies%' group by article

it give the following result

  article   cnt
 ----------------   
   ABC       8
   HIJ       1
   LMN       1
   PQR       2
   XYZ       3    
-----------------

In this result article ABC have only 4 viewers but in query result it showing 8

please help me to correct the query

Be careful what you count. Also, you have no PK on article_views. This may prove problematic. — Strawberry
– Strawberry, Commented Sep 3, 2015 at 12:32

Abhik Chakraborty · Accepted Answer · 2015-09-03 12:45:07Z

1

As already pointed out in the comment Be careful what you count so you need to get the count from article_view separately and then join back something as

select 
 t.article,
 act.cnt 
 from tags t 
 LEFT JOIN tag_master tm ON  t.tag_id=tm.id 
 INNER JOIN (
   select count(*) as cnt, article from article_view
   group by article
 )act
 ON t.article=act.article  
 WHERE 
 tm.tag_name LIKE '%movies%' 
 group by article

answered Sep 3, 2015 at 12:45

Abhik Chakraborty

44.9k6 gold badges56 silver badges63 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Strawberry Over a year ago

Well, not really. Properly, you need a PK on article_views, and then a COUNT of DISTINCT values returned from that table.

Vishu238 · Accepted Answer · 2015-09-03 12:32:12Z

1

Why dont you query from article_view table

select article,count(view_date) as cnt from article_view group by article

??

you should count view_date in your query

answered Sep 3, 2015 at 12:32

Vishu238

6735 silver badges18 bronze badges

3 Comments

Shameem Over a year ago

i need to connect with tag_master because when ever another tag came your query not suit for this

Vishu238 Over a year ago

that ok.. Did you try count(t.view_date) in your query

Vishu238 Over a year ago

Also you can user inner join instead of left join

Collectives™ on Stack Overflow

Duplicate result when using mysql join query

2 Answers 2

1 Comment

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related