How can i solve This Query in (SQL)?

I have Table Like which contain id,Name,Uid Like This:

--------------
Id Name Uid |
1 Ram 0 |
2 Sham 1 |
3 Cam 1 |
4 Dam 2 |
5 Eam 3 |
6 Fam 4 |
7 Gam 2
----------------


this is hierarchy Like:

Ram
|
--------------
| |
Sham Cam
| |
--------- ---
| | |
Dam Gam Eam


I want how many Workers are working under particular Person Like (under Ram 2 Persons are working)

Like Ram has id =1 and we will check in Uid, and all users which are having Uid=1 will be work under Ram

Output:

------------------------------------------------
Id Name Count(Number of Employee Working) |
1 Ram 2 |
2 Sham 2 |
3 Cam 1 |
4 Dam 0 |
5 Eam 0 |
6 Fam 0 |
7 Gam 0 |
------------------------------------------------


1 Answer
1

As it seems like you only want to know the number of direct children not traversing the subtree in greater depth, you can try:

SELECT t1.id,
t1.name,
(SELECT count(*)
FROM elbat t2
WHERE t2.uid = t1.id) count
FROM elbat t1;


By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.