Выберите количество записей из другой таблицы на основе объединения

Question

Я хочу получить количество записей, введенных пользователем в другую таблицу. Схема БД:

+-----------------------+  
| 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   |   
+----------------+------+

Я хочу видеть количество записей, созданных каждым пользователем в каждой таблице, примерно так:

  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  

После просмотра следующих ссылок:

1. Найти записи из разных таблиц
2. Выберите количество (*) из нескольких таблиц
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: объединить Select count(*) из нескольких таблиц

Мне удалось написать этот запрос, но он дает одинаковые записи во всех столбцах:

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

РЕДАКТИРОВАТЬ

Вот описание отношения:

1. FamilyMasterId - это внешний ключ в таблицах SurveyMaster и FamilyDetail.
2. FamilyDetailId - это внешний ключ в таблице TravelMaster.
3. TravelMasterId - это внешний ключ в таблице TravelDetail.

Answer 1

Возможно, это не идеальное решение, если учесть производительность, но оно может дать желаемый результат.

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;