optimize sql query

Question

I've a user table in my application.

Id  Name    ParentId
1   User1   0
2   User2   1
3   User3   1
4   User4   2
5   User5   2

The users have a relationship like grandparent->parent->child. to fetch the users related to a grand parent I'm using the following query

select * from user where ParentId=1 or ParentId in(select id from user where ParentId=1)

also these user have multiple roles in my role table and for fetching the count of rules a user having, I'm using select count(*) from group where userId=1.

Id  Role    UserId
1   Role 1  1
2   Role 2  1
3   Role 3  2
4   Role 4  2
5   Role 5  3

I need to fetch these both data in a single query .I'm not good with SQL and I know my first query is not perfect . How can I achieve this.

need to fetch these both data in a single query => How? Can you give sample data form group table as well and show your expected output based on that? — Utsav
– Utsav, Commented Jan 11, 2017 at 17:05

Gurwinder Singh · Accepted Answer · 2017-01-11 17:16:18Z

1

Your query is just need to be joined with your roles table.

SELECT u.id, count(r.id) no_of_roles
FROM user u left join roles r
on u.id = r.userid
WHERE u.ParentId=1
OR u.ParentId  IN
  (SELECT id FROM t WHERE ParentId=1
  )
group by u.id;

answered Jan 11, 2017 at 17:16

Gurwinder Singh

39.7k6 gold badges62 silver badges87 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

optimize sql query

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related