i have a table where i store member data with parent reference
if HU1 is parent ID i mean spos how do i count total mid under HU1 with nested child mid
for example see HU5 is under HU1 and HU6 is under HU5 then how do i count total nested MID which are under parent HU1
here total count under for HU1 is 10 how do i get this from sql query
i use mysql 5.0
i have tried
SELECT parent.mid, COUNT(child.mid) AS child_count FROM member parent INNER JOIN member child ON child.spos = parent.mid WHERE child.spos = 'HU1' GROUP BY parent.mid;
SELECT mid,spos,(SELECT count(unit) from member where spos=spos and spos='HU1')as totaspos from member where spos='HU1'
Not With Sql Query But I Have Manged it With Function i want to share the full page code, hope this will help someone
<!--#include file="mrraja.asp"-->
<style>
.propertyDetails td{text-align: left; padding-left: 1em;width: 10%;}
a{cursor: pointer;}
</style>
<div class="container">
<div class="col-md-12 wow fadeInLeft" data-wow-delay="0.4s">
<div class="living_box" style="height: 30em;overflow: auto;">
<%
Set allspos=TheDB.Execute("SELECT COUNT(*) as totdir FROM member where spos='"&iam("mid")&"' ORDER BY sl asc")
if not allspos.eof=true then
totdirme=allspos("totdir")
else
totdirme=0
end if
%>
<div class="living_desc">
<a href="#" class="btn3">Full Team Detail</a>
<p class="price">Total Directs: <%=totdirme%></p>
</div>
<table border="1" class="propertyDetails">
<tbody>
<tr>
<td>MID</td>
<td>NAME</td>
<td>SPONSOR</td>
<td>MOBILE</td>
<td>ACTIVE</td>
<td>COUNT</td>
</tr>
<%
Dim countrec:countrec=0
Dim countunit:countunit=0
Dim unt:unt=0
Dim tunt:tunt=0
call GET_HASAN(CStr(iam("mid")))
'===============
Public Sub GET_HASAN(MRRAJA)
sel = " SELECT mid FROM member where spos='" & MRRAJA & "' order by mid asc "
Set rs = TheDB.Execute(sel)
Do while Not rs.eof=true
If Not rs.EOF = True Then
l = rs.Fields(0)
Else
l = 0
End If
Call GET_IDEA1((l))
rs.MoveNext
Loop
rs.Close
Set rs = Nothing
End Sub
'------------------------------
Public Sub GET_IDEA1(MRRAJA)
call SET_ME(CStr(MRRAJA))
sel = " SELECT mid FROM member where spos='" & MRRAJA & "' order by mid asc "
Set rs = TheDB.Execute(sel)
Do while Not rs.eof=true
If Not rs.EOF = True Then
l = rs.Fields(0)
Call GET_IDEA1((l))
Else
l = 0
levelL=levelL+1
End If
rs.MoveNext
Loop
rs.Close
Set rs = Nothing
End Sub
'===============
Public Sub SET_ME(MRRAJA)
sel = " SELECT * FROM member where mid='" & MRRAJA & "' order by mid asc "
Set rs = TheDB.Execute(sel)
If Not rs.EOF = True Then
mid1=cstr(rs("mid"))
fname=rs("fullname")
snme=rs("spos")
sps=rs("spos")
mob=rs("mob")
address=(rs("addrs"))+" "+(rs("city"))+" "+(rs("dist"))
pkg=rs("act")
if pkg="YES" then
countunit=1
unt=unt+rs("unit")
else
countunit=0
unt=unt
end if
tunt=tunt+rs("unit")
mob=rs("mob")
End if
rs.close
Set rs = Nothing
%>
<tr>
<td><%=mid1%></td>
<td><%=fname%></td>
<td><%=snme%></td>
<td><%=mob%></td>
<td><%=pkg%></td>
<td><%=countunit%></td>
</tr>
<%End Sub%>
<tr>
<td colspan="2">Total: <%=tunt%></td>
<td colspan="2">Active: <%=unt%></td>
<td colspan="2">Inactive: <%=tunt-unt%></td>
</tr>
</tbody></table>
</div>
</div>
</div>
</div></div>