Select records counts from different table on the basis of join

Question

I want to get the count of records entered by a user in the different table. The schema of DB is:

+-----------------------+  
| Survey Master         |  
+----------------+------+   
| Field          | Key  |  
+----------------+------+  
| id             | PK   |  
| Username       |      |
| FamilyMasterId | FK   |
+----------------+------+

+------------+------+  
| Family Master     |  
+------------+------+   
| Field      | Key  |  
+------------+------+  
| id         | PK   |   
+------------+------+

+-----------------------+  
| Family Detail         |  
+----------------+------+   
| Field          | Key  |  
+----------------+------+  
| id             | PK   | 
| FamilyMasterId | FK   |   
+----------------+------+

+-----------------------+  
| Travel Master         |  
+----------------+------+   
| Field          | Key  |  
+----------------+------+  
| id             | PK   | 
| FamilyDetailId | FK   |   
+----------------+------+

+-----------------------+  
| Travel Detail         |  
+----------------+------+   
| Field          | Key  |  
+----------------+------+  
| id             | PK   | 
| TravelMasterId | FK   |   
+----------------+------+

I want to see the number of records created by each user in each table something like this:

  Username   SurveyMaster   FamilyMaster   FamilyDetail   TravelMaster   TravelDetail  
 ---------- -------------- -------------- -------------- -------------- -------------- 
  User001    59             47             36             26             12            
  User002    88             76             64             42             25            
  User003    49             44             35             25             15            
  User004    77             69             55             45             37  

After reviewing the following links:

1. Find Records from Different Tables
2. Select count(*) from multiple tables
3. http://www.sqlines.com/mysql/how-to/join-different-tables-based-on-condition
4. http://www.informit.com/articles/article.aspx?p=30875&seqNum=5
5. SQL: Combine Select count(*) from multiple tables

I was able to write this query but it gives the same records in all columns:

SELECT USERNAME, COUNT(USERNAME) SURVEYMASTER, COUNT(USERNAME) FAMILYMASTER, COUNT(USERNAME) FAMILYDETAIL, COUNT(USERNAME) TRAVELMASTER, COUNT(USERNAME) TRAVELDETAIL FROM 
((SELECT CREATEUSER USERNAME FROM SURVEYMASTER
) 
UNION ALL
(SELECT SM.CREATEUSER USERNAME FROM SURVEYMASTER SM
INNER JOIN FAMILYMASTER FM ON FM.ID = SM.FAMILYMASTERID
) 
UNION ALL
(SELECT SM.CREATEUSER USERNAME FROM SURVEYMASTER SM
INNER JOIN FAMILYMASTER FM ON FM.ID = SM.FAMILYMASTERID
INNER JOIN FAMILYDETAIL FD ON FM.ID = FD.FAMILYMASTERID
)
UNION ALL
(SELECT SM.CREATEUSER USERNAME FROM SURVEYMASTER SM
INNER JOIN FAMILYMASTER FM ON FM.ID = SM.FAMILYMASTERID
INNER JOIN FAMILYDETAIL FD ON FM.ID = FD.FAMILYMASTERID
INNER JOIN TRAVELMASTER TM ON FD.ID = TM.FAMILYDETAILID
)
UNION ALL
(SELECT SM.CREATEUSER USERNAME FROM SURVEYMASTER SM
INNER JOIN FAMILYMASTER FM ON FM.ID = SM.FAMILYMASTERID
INNER JOIN FAMILYDETAIL FD ON FM.ID = FD.FAMILYMASTERID
INNER JOIN TRAVELMASTER TM ON FD.ID = TM.FAMILYDETAILID
INNER JOIN TRAVELDETAIL TD ON TM.ID = TD.TRAVELMASTERID
)
) T
GROUP BY USERNAME
ORDER BY USERNAME

EDIT

Here is the relation description:

1. FamilyMasterId is the foreign key in SurveyMaster and FamilyDetail tables.
2. FamilyDetailId is the foreign key in TravelMaster table.
3. TravelMasterId is the foreign key in TravelDetail table.

Answer 1

This might not be the perfect solution if we consider performance but it can give the desired result

SELECT  sm.Username ,
        COUNT(*) SurveyMaster ,
        COUNT(FamilyMasterId) FamilyMaster ,
        fd.FamilyDetail ,
        tm.TravelMaster ,
        td.TravelDetail
FROM    SurveyMaster sm
        JOIN ( SELECT   Username ,
                        COUNT(fd.id) FamilyDetail
               FROM     SurveyMaster sm
                        JOIN FamilyMaster fm ON sm.FamilyMasterId = fm.Id
                        JOIN FamilyDetail fd ON fm.id = fd.FamilyMasterId
               GROUP BY Username
             ) fd ON sm.Username = fd.Username
        JOIN ( SELECT   Username ,
                        COUNT(tm.id) TravelMaster
               FROM     SurveyMaster sm
                        JOIN FamilyMaster fm ON sm.FamilyMasterId = fm.Id
                        JOIN FamilyDetail fd ON fm.id = fd.FamilyMasterId
                        JOIN TravelMaster tm ON fd.Id = tm.FamilyDetailId
               GROUP BY Username
             ) tm ON sm.Username = tm.Username
        JOIN ( SELECT   Username ,
                        COUNT(td.id) TravelDetail
               FROM     SurveyMaster sm
                        JOIN FamilyMaster fm ON sm.FamilyMasterId = fm.Id
                        JOIN FamilyDetail fd ON fm.id = fd.FamilyMasterId
                        JOIN TravelMaster tm ON fd.Id = tm.FamilyDetailId
                        JOIN TravelDetail td ON tm.Id = td.TravelMasterId
               GROUP BY Username
             ) td ON sm.Username = td.Username
GROUP BY sm.Username ,
        fd.FamilyDetail ,
        tm.TravelMaster ,
        td.TravelDetail;