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
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
