Saturday, 31 August 2013

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

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
ORDRE 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, can any one help me figuring it out.
Any help would be highly appreciated.
Thanks.

No comments:

Post a Comment