CODEWITHSUNDEEP
sqlsql-server
Optimus
Optimus

Reputation: 2210

optimize sql query

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.

Upvotes: 1

Views: 48

Answers (1)

Gurwinder Singh
Gurwinder Singh

Reputation: 39537

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;

Upvotes: 1

Related Questions