在一对多查询中使用SQL JOIN

Question

我希望有人能帮助我。我有两个表：widget表和reviews表。

widget
-------------------
widget_id   PK
id  (foreign key)
name
color
msrp
reviews
------------
review_id PK
widget_id (foreign key)
rating
source
review

我正在尝试为Codeigniter应用程序计算一个sql查询。场景如下：对于给定的制造商(widget.id)，返回所有的widget.name、widget.color、widget.msrp。对于这些返回的小部件，返回相应小部件的评论(即reviews.rating、reviews.source、reviews.review)。一个小部件可能有一个、多个或没有评论。

我使用的是Phil Stugeon的REST库，它以XML形式返回查询，因此返回的数据结构可能类似于以下内容：

<root>
<widget>
<name>widget-1</name>
<color>blue</color>
<msrp>75.50</msrp>
<reviews>
<review>
<rating>95</rating>
<source>some reviewer 1</source>
<review>great widget!</review>
</review>
<review>
<rating>65</rating>
<source>some reviewer 2</source>
<review>Poor widget.</review>
</review>
<reviews>
</widget>
<widget>
<name>widget-2</name>
<color>red</color>
<msrp>25.50</msrp>
</widget>
</root>

我尝试过使用子查询、foreach循环和GROUP_CONCT的解决方案；然而，对于类似的问题，Stackoverflow上的受访者建议使用JOIN。

即使使用JOIN，我也找不到正确的解决方案。这是我(缩写)的最后一次尝试，但不正确：

$widget = $this->db->query("SELECT
widget.name
, widget.color
, reviews.rating AS rating
FROM
widget
LEFT JOIN reviews
ON (widget.widget_id = reviews.widget_id)
WHERE widget.id = 46
GROUP BY widget.widget_id");

返回的XML:

<root>
<widget>
<name>widget-1</name>
<color>red</color>
<rating>92</rating>
</widget>
<widget>
<name>widget-2</name>
<color>green</color>
<rating>86</rating>
</widget>
<widget>
<name>widget-3</name>
<color>blue</color>
</widget>
</root>

我很有信心，我可以根据表名和列名构建缺失的review和review标记。主要问题是缺少行。根据数据库，小工具-1应该是这样的：

<root>
<widget>
<name>widget-1</name>
<color>red</color>
<reviews>
<review>
<rating>92</rating>
...
<review>
<review>
<rating>99</rating>
...
<review>
</reviews>
</widget>
…

我当前的查询只返回符合条件的第一行。(事实上，我把结果显示为XML是无关紧要的。我认为它可能比数组更容易阅读。)提前谢谢！

---

编辑

---

给定outis和vad-soft对GROUP BY的解释，我对这个问题有更好的处理。当我删除GROUP BY并运行查询时，返回以下内容：

<root>
<widget>
<name>widget-1</name>
<color>red</color>
<rating>92</rating>
</widget>
<widget>
<name>widget-1</name>
<color>red</color>
<rating>99</rating>
</widget>
<widget>
<name>widget-2</name>
<color>green</color>
<rating>86</rating>
</widget>
<widget>
<name>widget-3</name>
<color>blue</color>
</widget>
</root>

不幸的是，生成的XML需要符合特定的DTD，这要求与特定小部件相关的所有评论都包含在一个父标记中。你是否表示这是不可能的；或者有其他方法可以实现这一点；或者(很可能)我错过了一个关键点-再次感谢。

Answer 1

在您的场景中，GROUP BY并不意味着因为您想要特定id的详细信息，并且基于此您将获得引用表的详细信息。相反，在不使用GROUP BY子句的情况下使用相同的查询，因此它也会在子表中查找相同id的所有记录。因此，它会将主表数据重复为widget.name、widget.color、widget.msrp，但不要放在这里的widget.id。希望这能有所帮助。

$widget = $this->db->query("SELECT
widget.name
, widget.color
, reviews.rating AS rating
FROM
widget
LEFT JOIN reviews
ON (widget.widget_id = reviews.widget_id)
WHERE widget.id = 46");

感谢