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

adding some data to caseSummaries would help – Paul Maxwell Oct 19 '14 at 11:54 — Paul Maxwell, Oct 19 '14 at 11:54

score 0 · Accepted Answer · answered Oct 19 '14 at 12:13

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

answered Oct 19 '14 at 12:13

Paul Maxwell

33,002
3
32
51

Thanks, due to the time and confidentiality of the raw data I didn't add anything to the case summaries table. I got what I wanted by grouping only by caseID as it still repeated cases when I added the other fields for some reason and I was able to add a third table to the group_concat so thank you very much for your help. – Clement Oke Oct 23 '14 at 10:55
**"it still repeated cases when I added the other fields"** Yes, SQL will repeat values - this is not wrong, it is expected and eventually you will understand why I hope. To arrive at a single row with multiple rows of source data you need something special (like group_concat), so well done. Cheers. – Paul Maxwell Oct 23 '14 at 10:59
One more problem has surfaced, I've now got the group_concat doubling up my reults. I'll post another fiddle probably put it in a new question – Clement Oke Oct 23 '14 at 11:58

Trying to create one table from four

1 Answers1

Linked