a great select query including count

I have around 20 tables including examination records of students. I tried to figure out my question (Student ID = sID, i.e. Score 1 : s1) : StudentsTables : (student reg.data)

sID   name   surname   regDate     photo           ........
891   Mike   Jackson   01.01.2013  82342984.png    ....

TableA : (student scores)

sID   exam    s1    s2    s3    s4
891   6       0     0     0     20  > student 891 attended exam 6
891   10      30    80    100   75  > student 891 attended exam 10

(count = 2)

TableB : (student documents , i.e. : document 1 : d1)

sID   d1      d2     d3     d4     d5     d6
891   true    false  true   true   true   true

(count = 1)

TableC : (student messages)

mID   from    to    subject    message
1     10      891   any sub.   any message... > student 891 received messages (look "to")
1     10      891   mes2       other message... 
1     29      891   mes3       another message...

(count = 3)

TableD : (student payments)

sID   pID    amount    date   details

(no student record in this table, count = 0)

..... and similar tables like above.

I want a result like below :

sID   name   surname   scoreCount   docCount   messageCount   paymentCount .....
891   Mike   Jackson   2            1          3              0            ...
892   Susan  Button    0            3          10             0            ...
893   Ahmad  Malisi    1            0          5              2            ...
894   any    any       4            1          0              0            ...
...
..

Solution

Have you tried this

Select sid,name,surname,
       (Select count(*) From student_scores Where Sid = S.Sid ) as scoreCount ,
       (Select count(*) From student_documents Where Sid = S.Sid ) as docCount ,
       (Select count(*) From student_messages Where to= S.Sid ) as messageCount  ,
       (Select count(*) From student_payments Where Sid = S.Sid ) as paymentCount ,
        .
        .
        .
From StudentsTables S
Where Sid=891