Trying to create one table from four

Question

I'm stuck trying to create a query that pulls results from at least three different tables with many to many relationships.

I want to end up with a table that lists cases, the outcomes and complaints.
All cases may have none, one or multiple outcomes, same relationship applies to the complaints. I want to be able to have the case listed once, then subsequent columns to list all the outcomes and complaints related to that case. I have tried GROUP_CONCAT to get the outcomes in one column instead of repeating the cases but when I use UNION to combine the outcomes and complaints one column header overwrites the other.

Any help appreciated and here's the link to the fiddle http://sqlfiddle.com/#!2/d111e/2/0

Answer 1

I suggest you START with this this query structure:

SELECT
        c.caseID, c.caseTitle, c.caseSynopsis /* if more columns ... add to group by also */
      , group_concat(co.concern)
      , group_concat(re.resultText)
FROM caseSummaries AS c 

LEFT JOIN JNCT_CONCERNS_CASESUMMARY AS JCC ON c.caseID = JCC.caseSummary_FK
LEFT JOIN CONCERNS AS co ON JCC.concerns_FK = co.concernsID

LEFT JOIN JNCT_RESULT_CASESUMMARY AS JRC ON c.caseID = JRC.caseSummary_FK
LEFT JOIN RESULTS AS re ON JRC.result_FK = re.result_ID

GROUP BY
        c.caseID, c.caseTitle, c.caseSynopsis /* add more ... here also */
;

Treat the table caseSummaries as the most important and then everything else "hangs off" that.

Please note that although MySQL will allow it, you should place EVERY non-aggregating column that you include in the select clause into the group by clause also.

also see: http://sqlfiddle.com/#!2/2d1a79/7