CODEWITHSUNDEEP
mysqlsqldatabasesubquery
Murtaza Malek
Murtaza Malek

Reputation: 303

How to use a subquery inside another subquery with three tables in mysql?

I have three tables in my database, see the structure below :

users

uid | fname | date_joined
---------------------------------
 1  | john  | 2013-08-25 01:00:00
 2  | rock  | 2013-08-26 01:00:00
 3  | jane  | 2013-08-27 01:00:00

questions

qid | uid 
---------- 
 1  |  1   
 2  |  1   
 3  |  2   
 4  |  3   
 5  |  3   
 6  |  1   
 7  |  1   
 8  |  2   
 9  |  2

followers

fid | qid
---------
 1  |  2
 2  |  1
 3  |  2
 4  |  1
 5  |  2
 6  |  3
 7  |  2

user table contains all user related fields
questions table contains all question related data with the foreign key uid
followers table stores the information of how many times a question followed

What I want my query to return is : 

unique uid, 
fname
question count for each user
follower count for each user

I have written a query and its working fine and returning the records as I want but the followers count is always 0. Here is my query :

SELECT 
    u.uid, u.fname, u.date_joined , 
    (SELECT COUNT(*) FROM questions WHERE questions.uid = u.uid) AS question_count,
    (SELECT COUNT(*) FROM followers WHERE followers.qid IN ( 
        SELECT GROUP_CONCAT(qid) FROM questions WHERE questions.uid = u.uid 
        ) 
    ) AS follow_count
FROM epc_user AS u
ORDER BY follow_count DESC, question_count DESC, date_joined DESC

I tried several different combinations but none of them worked, maybe I am writing a wrong query or its not possible to use subquery in another subquery, whatever it may be. I just want to know if its possible or not and if possible

Upvotes: 4

Views: 20998

Answers (2)

slaakso
slaakso

Reputation: 9080

You can use a joins to get the result. No need for subqueries:

select 
  u.uid, 
  u.fname, 
  u.date_joined, 
  count( distinct q.qid ) as 'question_count', 
  count( distinct f.fid ) as 'follower_count'
from users u
  left join questions q on q.uid = u.uid
  left join followers f on f.qid = q.qid
group by u.uid, u.fname, u.date_joined
order by follower_count desc, question_count desc, date_joined desc;

Upvotes: 2

Ashish Gaur
Ashish Gaur

Reputation: 2050

GROUP_CONCAT will return a string but the IN operator works with a set of values and not with a string so you have to use something like this

   SELECT 
        u.uid, u.fname, u.date_joined , 
        (SELECT COUNT(*) FROM questions WHERE questions.uid = u.uid) AS question_count,
        (SELECT COUNT(*) FROM followers WHERE followers.qid IN ( 
            SELECT qid FROM questions WHERE questions.uid = u.uid 
            ) 
        ) AS follow_count
    FROM users AS u
    ORDER BY follow_count DESC, question_count DESC, date_joined DESC

fiddle: http://sqlfiddle.com/#!2/8cd10/2

Upvotes: 3

Related Questions