mysql - una - normalizacion de base de datos ejemplos

ÚNASE a GROUP BY en una base de datos normalizada sobre recursos, temas y capítulos

Question

He normalizado mi base de datos, pero parece que no puedo devolver los datos que estoy buscando de la manera correcta.

Tengo 5 tablas:

1. Recursos (5 recursos)
2. Temas (10 temas)
3. Capítulos (10 capítulos)
4. Temas-a-Recursos (18 temas a enlaces de recursos)
5. Temas a capítulos (18 temas a enlaces de capítulos)

Mira este SQL Fiddle ...

Necesito recopilar todos los registros en la tabla de Recursos y agruparlos con sus correspondientes temas y capítulos (de las tablas Temas-a-Recursos y Temas-a-Capítulos)

¿Alguien puede sugerir la consulta SQL correcta para devolver los 5 registros de recursos con sus temas y capítulos?

Intenté UNIRSE a GROUP BY y esto condensa el conjunto de registros con los 5 recursos, pero no con toda la demás información que necesito (temas y capítulos).

SELECT * FROM TOPICS, CHAPTERS, RESOURCES AS RES
INNER JOIN TOPICS_to_RESOURCE AS TR ON RES.RES_ID = TR.TR_RESID
INNER JOIN TOPICS_to_CHAPTER AS TCH ON TR.TR_TID = TCH.TCH_TID
GROUP BY RES.RES_ID

Answer 1

Esta es una sintaxis no estándar:

SELECT *
...
GROUP BY RES.RES_ID

Aquí ha solicitado cada columna (seleccione *) pero solo ha especificado una columna debajo del grupo. SOLAMENTE MySQL permite esta sintaxis GROUP BY no estándar que está controlada por la configuración del servidor. Esas configuraciones predeterminadas han cambiado recientemente y es posible que muchas de nuestras consultas que usan GROUP BY no estándar fallen en el futuro.

Una cláusula GROUP BY que cumple con los estándares especifica TODAS las columnas "no agregadas". Lo que significa que debe especificar cada columna que no utiliza SUMA / CONTAR / AVG / MIN / MAX etc.

SELECT * FROM TOPICS, CHAPTERS, RESOURCES AS RES INNER JOIN TOPICS_to_RESOURCE AS TR ON RES.RES_ID = TR.TR_RESID INNER JOIN TOPICS_to_CHAPTER AS TCH ON TR.TR_TID = TCH.TCH_TID GROUP BY RES.RES_ID

Esa consulta tiene 2 formas de sintaxis de unión. ** FROM t1, t2, t3 ** es antiguo e imprudente y NO es una buena práctica. También; nunca "combine" este antiguo formulario de sintaxis con la sintaxis de unión más reciente en una única consulta, ya que puede generar errores de consulta.

Simplemente NO uses comas entre tablas en la cláusula from. Este simple paso te hará usar la mejor sintaxis todo el tiempo.

Por cierto DE TEMAS, CAPÍTULOS, RECURSOS COMO RES, sin nada que los limite en una cláusula where producirá:

Multiplique cada fila en TEMAS por cada fila en CAPÍTULOS por cada fila en RECURSOS. En otras palabras, un "producto cartesiano". En sintaxis más reciente, su consulta se traduce a:

SELECT * FROM TOPICS CROSS JOIN CHAPTERS CROSS JOIN RESOURCES AS RES INNER JOIN TOPICS_to_RESOURCE AS TR ON RES.RES_ID = TR.TR_RESID INNER JOIN TOPICS_to_CHAPTER AS TCH ON TR.TR_TID = TCH.TCH_TID GROUP BY RES.RES_ID

Answer 2

Realmente no puedo distinguir lo que estás tratando de lograr, pero parece que simplemente buscas obtener una tabla que muestre cada capítulo con su tema y recurso.

Si es así, entonces el siguiente SQL:

select * from resources r
JOIN topics_to_resource ttr ON ttr.tr_resid = r.res_id
JOIN topics t on t.t_id = ttr.tr_tid
JOIN topics_to_chapter ttc on ttc.tch_tid = t.t_id
JOIN chapters ch ON ch.ch_id = tch_chid
ORDER BY r.res_id;

devolverá solo eso, según http://sqlfiddle.com/#!9/ddf252/12

O bien, ignorando las ID de unión en la selección:

select r.res_id, r.res_name, t.t_id, t.t_name, ch.ch_id, ch.ch_name from resources r
JOIN topics_to_resource ttr ON ttr.tr_resid = r.res_id
JOIN topics t on t.t_id = ttr.tr_tid
JOIN topics_to_chapter ttc on ttc.tch_tid = t.t_id
JOIN chapters ch ON ch.ch_id = tch_chid
ORDER BY r.res_id, t.t_id, ch.ch_id

según http://sqlfiddle.com/#!9/ddf252/14

Si eso no es lo que estás buscando, ¿podrías elaborar un poco sobre los resultados que estás buscando?

Editar : para devolver una lista más concisa con todos los registros asociados

select 
CONCAT(r.res_id,'': '',r.res_name) ''Resources'', 
GROUP_CONCAT(CONCAT('' ('',t.t_id,'': '',t.t_name,'')'')) ''Topics'', 
GROUP_CONCAT(CONCAT('' ('',ch.ch_id,'': '',ch.ch_name,'')'')) ''Chapters''
from resources r
JOIN topics_to_resource ttr ON ttr.tr_resid = r.res_id
JOIN topics t on t.t_id = ttr.tr_tid
JOIN topics_to_chapter ttc on ttc.tch_tid = t.t_id
JOIN chapters ch ON ch.ch_id = tch_chid
GROUP BY r.res_id
ORDER BY r.res_id, t.t_id, ch.ch_id

Según http://sqlfiddle.com/#!9/ddf252/30

Finalmente , para agruparlos por capítulo y tema:

select 
CONCAT(res_id,'': '',res_name) ''Resources'', 
GROUP_CONCAT(`chapters` order by chapters separator ''/n'') as ''Content''
FROM
  (SELECT r.res_id ''res_id'',
          r.res_name ''res_name'', 
          t.t_id ''t_id'',
          t.t_name ''t_name'',
          CONCAT(t.t_name,'': ('',GROUP_CONCAT(ch.ch_name ORDER BY t.t_name separator '',''),'')'') ''Chapters''
    FROM resources r
      JOIN topics_to_resource ttr ON ttr.tr_resid = r.res_id
      JOIN topics t on t.t_id = ttr.tr_tid
      JOIN topics_to_chapter ttc on ttc.tch_tid = t.t_id
      JOIN chapters ch ON ch.ch_id = tch_chid
    GROUP BY res_id, t_id
    ORDER BY r.res_id, t.t_id, ch.ch_id) as t
GROUP BY res_id

Como se ve aquí: http://sqlfiddle.com/#!9/ddf252/85

He revisado los resultados, y se ven bien, pero comprueben, ya que se ha ido un poco como el inicio de MySQL en mi cabeza (es más allá de la 1 de la madrugada)

Además adicional: valores distintos por recurso

    select CONCAT(r.res_id,'': '',r.res_name) ''Resources'', GROUP_CONCAT(distinct t_name separator '','') ''Topics'', 
GROUP_CONCAT(distinct ch.ch_name separator '','') ''Chapters''
from resources r
JOIN topics_to_resource ttr ON ttr.tr_resid = r.res_id
JOIN topics t on t.t_id = ttr.tr_tid
JOIN topics_to_chapter ttc on ttc.tch_tid = t.t_id
JOIN chapters ch ON ch.ch_id = tch_chid
GROUP BY r.res_id
ORDER BY r.res_id, t.t_id, ch.ch_id

Ver http://sqlfiddle.com/#!9/ddf252/88