Joining strings in relation to JOIN

Question

Suppose I have a cooking show:

cookingepisodes id | date --------------- 1 | A 2 | B 3 | C 4 | D … 

This show presents products in these categories (left) and is linked by a table to the right:

 tests testitems id | name id | episodeid | testid | name ------------ ------------------------------------ 1 | cutlery 1 | 1 | 1 | Forks 2 | spices 2 | 2 | 1 | Knives 3 | 4 | 1 | Spoons 4 | 4 | 2 | Oregano 

My desired result :

 showid | testid | testname 4 | 1,2 | cutlery, spices 3 | NULL | NULL 2 | 1 | cutlery 1 | 1 | cutlery 

I tried using this query, and it works until I need to concatenate the results (when there are two tests in one episode). Then the connection will create several rows based on the quantity

 SELECT DISTINCT e.*, i.testid, t.name AS testname FROM cookingepisodes AS e LEFT OUTER JOIN testitems AS i ON i.episodeid = e.id LEFT OUTER JOIN tests AS t ON i.testid = t.id ORDER BY e.date DESC 

I also tried something like this, but I can't get it to work due to an external block reference (e.id):

 JOIN ( SELECT GROUP_CONCAT(DISTINCT testid) FROM testitems WHERE testitems.episodeid = e.id ) AS i 

Any tips on how I can solve this problem without restructuring the database?

Answer 1

Try this option -

 SELECT ce.id showid, GROUP_CONCAT(te.testid) testid, GROUP_CONCAT(t.name) testname FROM cookingepisodes ce LEFT JOIN testitems te ON te.episodeid = ce.id LEFT JOIN tests t ON t.id = te.testid GROUP BY ce.id DESC;