JOIN with GROUP BY in a Normalized DB about Resources, Topics & Chapters

Question

I've normalized my DB but can't seem to return the data I'm looking for in the right way.

I've got 5 tables:

1. Resources (5 resources)
2. Topics (10 topics)
3. Chapters (10 chapters)
4. Topics-to-Resources (18 topic to resource links)
5. Topics-to-Chapters (18 topic to chapter links)

Check out this SQL Fiddle...

I need to collect all the records in the Resources table and group each of them with their corresponding topics and chapters (from the Topics-to-Resources and Topics-to-Chapters tables)

Can anyone suggest the right SQL query to return the 5 resource records with their attending topics and chapters?

I've tried JOINS with GROUP BY and this condenses the recordset to the 5 resources but not with all the other information I need (topics and chapters).

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

I can't really distinguish what you're trying to achieve, but it sounds like you're simply looking to obtain a table that shows every chapter with its topic and resource.

If so, then the following 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;

will return just that, as per http://sqlfiddle.com/#!9/ddf252/12

Or, ignoring the join IDs in the select:

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

as per http://sqlfiddle.com/#!9/ddf252/14

If that's not what you're looking for, could you elaborate a little on what results you're looking to see?

Edit: To return a more concise list with all associated records

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

As per http://sqlfiddle.com/#!9/ddf252/30

Finally, to group these by chapter and topic:

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

As seen here: http://sqlfiddle.com/#!9/ddf252/85

I've checked the results over, and they look fine - but double check, as it's gone a bit like MySQL Inception in my head (it's past 1am here)

Further addition: Distinct values per resource

    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

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

Answer 2

This is non-standard syntax:

SELECT *
...
GROUP BY RES.RES_ID

Here you have asked for every column (select *) but only specified one column under group by. ONLY MySQL allows this non-standard GROUP BY syntax which is controlled by server settings. Those default settings have recently changed and you may find many our queries using non-standard GROUP BY could fail in future.

A standards compliant GROUP BY clause specifies ALL "non-aggregating" columns. Meaning you must specify every column not using SUM/COUNT/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

That query has 2 forms of join syntax. **FROM t1,t2,t3 ** is ancient and unwise and is NOT good practice. Also; never "combine" this old syntax form with the more recent join syntax in a single query as that can lead to query errors.

Just do NOT use commas between tables in the from clause this simple step will make you use the better syntax all he time.

By the way FROM TOPICS, CHAPTERS, RESOURCES AS RES with nothing to limit these in a where clause will produce:

Multiply every row in TOPICS by every row in CHAPTERS by every row in RESOURCES. In other words a "Cartesian product". In more recent syntax your query translates to:

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