relaciones - relacionar tablas en sql server por codigo

Consulta de uno a varios seleccionando todos los padres y un único superior para cada padre

Question

Hay dos tablas de SQL:

Parents:
+--+---------+
|id|   text  |
+--+---------+
| 1|  Blah   |
| 2|  Blah2  |
| 3|  Blah3  |
+--+---------+

Childs
+--+------+-------+
|id|parent|feature|
+--+------+-------+
| 1|   1  |  123  |
| 2|   1  |   35  |
| 3|   2  |   15  |
+--+------+-------+

Quiero seleccionar con una sola consulta cada fila de la tabla de Padres y para cada una de las filas de la tabla de Niños con la relación "padre" - valor de "id" y el valor más alto de la columna "característica". En este ejemplo el resultado debería ser:

+----+------+----+--------+---------+
|p.id|p.text|c.id|c.parent|c.feature|
+----+------+----+--------+---------+
|  1 | Blah |  1 |    1   |    123  |
|  2 | Blah2|  3 |    2   |    15   |
|  3 | Blah3|null|   null |   null  |
+----+------+----+--------+---------+

Donde p = tabla padre y c = tabla hijo

Intenté DEJAR A UNIRSE EXTERNO y GRUPAR POR, pero MSSQL Express me dijo que la consulta con GRUPO POR requiere funciones de Agregado en todos los campos no agrupados. Y no quiero agruparlos a todos, sino seleccionar la fila superior (con orden personalizado).

Estoy totalmente sin ideas ...

Answer 1

Esto debería funcionar:

SELECT p.id, p.text, c.id, c.parent,c.feature
FROM parent p
 LEFT OUTER JOIN (SELECT TOP 1 child.id,
                               child.parent,
                               MAX(child.feature)
                  FROM child
                  WHERE child.parent = p.id
                  GROUP BY child.id, child.parent
                  ) c ON p.id = c.parent

Answer 2

La consulta de manji no maneja los desempates para la característica máxima. Aquí está mi método, que he probado:

;WITH WithClause AS (SELECT p.id, p.text, 
        (SELECT TOP 1 c.id from childs c 
            where c.parent = p.id order by c.feature desc) 
        AS BestChildID
    FROM Parents p) 
SELECT WithClause.id, WithClause.text, c.id, c.parent, c.feature
FROM WithClause 
LEFT JOIN childs c on WithClause.BestChildID = c.id

Answer 3

Si necesita unir diferentes de una columna MAX y de cualquier columna descrita en un grupo cerrando una selección anidada, puede usar una función APLICAR. Es la solución más sencilla. También puede utilizar el operador WITH. Pero eso parece más difícil.

SELECT p.id, p.text, CHILD_ROW.ANY_COLLUMN
FROM parent p
OUTER APPLY (SELECT TOP 1 child.ANY_COLLUMN
                  FROM child
                  WHERE child.parent = p.id
                  ORDER BY child.feature DESC 
                  ) CHILD_ROW

Answer 4

Utilizando CTE (SQL Server 2005+):

WITH max_feature AS (
   SELECT c.id,
          c.parent,
          MAX(c.feature) ''feature''
     FROM CHILD c
 GROUP BY c.id, c.parent)
   SELECT p.id,
          p.text,
          mf.id,
          mf.parent,
          mf.feature
     FROM PARENT p
LEFT JOIN max_feature mf ON mf.parent = p.id

Equivalente no CTE:

   SELECT p.id,
          p.text,
          mf.id,
          mf.parent,
          mf.feature
     FROM PARENT p
LEFT JOIN (SELECT c.id,
                  c.parent,
                  MAX(c.feature) ''feature''
             FROM CHILD c
         GROUP BY c.id, c.parent) mf ON mf.parent = p.id

Su pregunta carece de detalles para manejar los desempates (cuando los valores 2+ CHILD.id tienen el mismo valor de característica). La respuesta de Agent_9191 usa TOP 1 , pero tomará el primero que se devuelve y no necesariamente el que usted desea.

Answer 5

select p.id, p.text, c.id, c.parent, c.feature
from Parents p
left join (select c1.id, c1.parent, c1.feature
             from Childs c1
             join (select p1.id, max(c2.feature) maxFeature
                     from Parents p1
                left join Childs c2 on p1.id = c2.parent
            group by p1.id) cf on c1.parent = cf.id 
                              and c1.feature = cf.maxFeature) c
on p.id = c.parent