CODEWITHSUNDEEP
mysqlleft-join
KuKu
KuKu

Reputation: 646

mysql left join and query with where in clause showing only 1 row

As i am making a simple forum with the touch of twitter like follower and following, i have following tables.

users

 1. id
 2. name

questions

 1. id
 2. description
 3. user_id//foreign key of users.id

answers

 1. id
 2. question_id//foreign key of questions.id
 3. description
 4. user_id//foreign key of users.id

follow

 1. from_user(which user)//foreign key of users.id
 2. to_user(following to which user)//foreign key of users.id

My query is as follow

SELECT users.id, users.name, count(DISTINCT questions.id), count(answers questions.id), count(follow1.from_user), count(follow2.to_user) from users LEFT JOIN questions ON users.id = questions.user_id LEFT JOIN answers ON users.id = answers.user_id LEFT JOIN follow as follow1 ON users.id = follow1.from_user LEFT JOIN follow as follow2 ON users.id = follow2.to_user WHERE users.id = 1

This works fine and give the proper result.

But when with the same query i put the clause of WHERE IN as follows

SELECT users.id, users.name, count(DISTINCT questions.id), count(answers questions.id), count(follow1.from_user), count(follow2.to_user) from users LEFT JOIN questions ON users.id = questions.user_id LEFT JOIN answers ON users.id = answers.user_id LEFT JOIN follow as follow1 ON users.id = follow1.from_user LEFT JOIN follow as follow2 ON users.id = follow2.to_user WHERE users.id IN (1, 3)

It shows the record only for user id 1. Not for 3. But talking about the previous query works fine for both id's and show the proper records.

Upvotes: 0

Views: 122

Answers (1)

Question Mark
Question Mark

Reputation: 3606

That is an odd one, i can only suggest using GROUP BY users.id so that mysql doesn't combine the count()s over all records. Hope that in some way helps.

Upvotes: 1

Related Questions