尝试从四个表中创建一个表 [英] Trying to create one table from four

问题描述

我一直在努力创建一个查询，该查询从至少三个具有多对多关系的不同表中提取结果.

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

我想最后得到一个表格，其中列出了案件，结果和投诉.
所有案件可能没有结果，一个或多个结果，相同的关系适用于投诉.我希望能够将案件列出一次，然后在随后的列中列出与该案件有关的所有结果和投诉. 我曾尝试GROUP_CONCAT将结果显示在一个列中，而不是重复这些案例，但是当我使用UNION将结果和投诉合并在一起时，一个列标题会覆盖另一列标题.

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.

感谢任何帮助，这是小提琴的链接 http://sqlfiddle.com /#！2/d111e/2/0

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 */
;

将表caseSummaries视为最重要的内容，然后其他所有操作都将其挂掉".

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

请注意，尽管MySQL允许，但您还应将select子句中包含的每个非聚合列都放入group by子句中.

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.

另请参见: http://sqlfiddle.com/#!2/2d1a79/7

这篇关于尝试从四个表中创建一个表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！