Need Mysql Query ( Thread Changed)

Question

Hi all,

I have changed my thread as my last thread also same like that. But actually this is my requirement.

I have n number of users with different articleId. If I pass the articleId, I want to get the results like how many different users viewed that article.

table1:

recId   userId   articleId     
-----   ------    -------- 
1        10111        102    
2        10121        102    
3        10111        102       
4        10121        105        
5        10111        102        
6        10122        105      

table2:

userId     jobtitle        
-----       ------            
10111       Engineer          
10121       Doctor
10122       Professor            

If I pass the articleId as "102" from Table1, It should return the details like how many times that particular article viewed by different users like:

jobtitle   total 
-----      ------     
Engineer    3 
Doctor      1 
Professor   1 

if i pass articleid as "105", result will be like this:

jobtitle   total 
-----      ------     
Doctor     1
Professor  1

How to write query for the above results?

Thanks in advance

• Gnaniyar Zubair

Answer 1

SELECT
  jobtitle, COUNT(recID) as total
  FROM table1 a
  INNER JOIN table2 b
  ON
    a.userId = b.userId
  GROUP BY
    jobtitle
  WHERE articleId = ?

Answer 2

SELECT jobtitle, COUNT(*) AS total
FROM table1
JOIN table2 ON ( table1.userId = table2.userId )
WHERE articleId = <your articleid>
GROUP BY table2.jobtitle