CODEWITHSUNDEEP
phpmysql
Passionate Coder
Passionate Coder

Reputation: 7294

Mysql query to get total childs?

I have a 4 tables in which i need to fetch users info, state name , branch name and total no of childs from db.

I used below sql

SELECT  `user_id` ,  `user_name` , state_name, branch_name
FROM  `user` 
LEFT JOIN state ON state_id = user_state_id
LEFT JOIN branch ON branch_id = user_branch_id

But i also need to get count of childs for each user so i used below query

SELECT  `user_id` ,  `user_name` , state_name, branch_name, count(child_id)
FROM  `user` 
LEFT JOIN state ON state_id = user_state_id
LEFT JOIN branch ON branch_id = user_branch_id
LEFT JOIN child on parent_id = user_id  group by user_id

I need to ask one question it it ok to use group by for count in whole query Is this affect data which come with joins from this query

Upvotes: 0

Views: 87

Answers (2)

Rwd
Rwd

Reputation: 35220

It will probably be easier to use a subquery for this:

SELECT  `user_id` ,  `user_name` , state_name, branch_name
(SELECT count(*) FROM `child` WHERE `parent_id` = `user_id`) as `child_count` 
FROM  `user` 
LEFT JOIN state ON state_id = user_state_id
LEFT JOIN branch ON branch_id = user_branch_id

Hope this helps!

Upvotes: 1

Praveen E
Praveen E

Reputation: 976

There are three tables involved in your JOIN. As you are GROUPING BY user_id, only one row per user_id will be in the output. Let us take the first table state. If an user is having only one state then GROUP BY won't cause any problem as this one row will be in the output. Same applies to the branch.

For the last table 'child', if a user is having multiple childs (obviously as per your query), only one row will be present in the output. As you are using count(child_id) alone in the 'SELECT', this count alone will be in the output. If you have any other column in the SELECT from the 'child' table other than 'count', only the value from the first child will be in the output.

As long as you have only one row per user in state table and branch table, output won't be affected by your GROUP BY.

Upvotes: 1

Related Questions