SQL: Obtener recuento de muchas tablas para un logging de usuario en la tabla USER. ¿Cuál es el mejor enfoque?

Tengo 4 tablas de SQL Server (versión 2008)

1) USER- to store user information (Fields : UserId,UserName) 2) FILES - to store files uploaded by user (FileId,FileName,UserId) 3) PHOTOS -to store files uploaded by user (PhotoId,PhotoName,UserId) 4) GROUPS= to store groups created by user ( GroupId,GroupName,UserId) 

Ahora quiero get un logging de usuario con ID = 5 junto con el número total de files cargados por ID de usuario 5, fotos totales cargadas por ID de usuario 5, grupos totales creados por ID de usuario 5. ¿Puedo get todos estos con una sola consulta? O debería escribir un SELECT COUNT de cada tabla con USER ID = 5 en la cláusula where (3 consultas …)

Puedo escribir una combinación interna con USUARIO y ARCHIVOS y hacer el recuento para get la cantidad de files cargados por un usuario particular. Pero, ¿cómo agregar el número de fotos y el número de grupos al resultado?

¿Cuál es el mejor enfoque para hacer esto?

Data de muestra

USUARIO

 USERID USERNAME ------ --------- 1 Shyju 2 OMG 3 Gus 

ARCHIVOS

 FILEID FILENAME USERID ------ -------- ------ 101 Sample1 1 102 Secondone 1 103 NewOne 2 104 BetterOne 3 105 Ocean 3 106 Sea 3 

GRUPOS

 GROUPID GROUPNAME USERID ------- --------- ------ 51 Group-A 1 52 Group-B 2 53 Group-C 2 54 Group-D 2 55 Group-E 3 56 Group-F 3 

El resultado que estoy buscando es

Para ID de usuario = 1

  USERID USERNAME FILECOUNT GROUPCOUNT ------ -------- --------- ---------- 1 Shyju 2 1 

Para UserId = 2

  USERID USERNAME FILECOUNT GROUPCOUNT ------ -------- --------- ---------- 2 OMG 1 3 

Utilizar:

  SELECT u.userid, u.username, COALESCE(f.numFiles, 0) AS numFiles, COALESCE(p.numPhotos, 0) AS numFiles, COALESCE(g.numGroups, 0) AS numGroups FROM [USER] u LEFT JOIN (SELECT t.userid, COUNT(*) AS numFiles FROM [FILES] t GROUP BY t.userid)f ON f.userid = u.userid LEFT JOIN (SELECT t.userid, COUNT(*) AS numPhotos FROM [PHOTOS] t GROUP BY t.userid) p ON p.userid = u.userid LEFT JOIN (SELECT t.userid, COUNT(*) AS numGroups FROM [GROUPS] t GROUP BY t.userid) g ON g.userid = u.userid WHERE u.userid = 2 

Tienes que usar uniones OUTER para que esto esté en una consulta; Las combinaciones INNER para todas las tablas requerirían que el usuario tenga al less un logging en la tabla de ARCHIVOS, FOTOS y GRUPOS para estar en el set de resultados. Una unión EXTERIOR significa que los usuarios con loggings en al less una de las tablas (ARCHIVOS, FOTOS o GRUPOS) serán devueltos.

Pero JOIN también corre el riesgo de inflar el set de resultados, que es el problema que ocurrió en la versión anterior de mi respuesta. Al cambiar la consulta para usar tablas derivadas / vistas en línea para los recuentos de ARCHIVOS, GRUPOS y FOTOS, se resuelve el problema y no es necesario agrupar BY fuera de las tablas derivadas / vistas en línea.