一个到多个关系的SQL查询

I have two tables, Table A and Table B. For each record in table A there are many records in Table B; thus, a one to many relationship exists between tables A and B. I want to perform a query so that for each row returned from table A, all of the corresponding rows will be returned from table B. From what I understand I'll need to use a INNER Join - however, how would I go about accessing all of the returned rows through say, PHP?

我有两个表，表A和表B。表A中的每个记录在表B中有很多记录;因此,表之间存在一对多的关系,我想为每一行执行一个查询,以便从表返回,返回所有相应的行从表b .我理解我需要使用内连接——然而,我如何通过说,访问所有返回的行PHP吗?

$sql = "Select A.ID, B.Name * From A INNER JOIN B ON A.ID = B.ID";

A.ID  |  B.fName        |  B.lName
1          nameone         lnameone
1          nametwo         lnametwo
2          namethree       lnamethree
4          namefour        lnamefour

Now that I have the above results, I want to use PHP to loop through all of the values of B.Name only for a single A.ID at a time. So, the results I want would look like:

现在我有了以上的结果，我想用PHP来遍历所有的B值。只为一个a命名。ID。所以，我想要的结果是:

1.
nameOne lNameOne
nameTwo lnametwo

2. namethree lNamethree

4. nameFour lNameFour

Basically, I'm trying to group the query results by the ID in table A.

基本上，我试图通过表A中的ID对查询结果进行分组。

I appreciate the help very much!

我非常感谢你的帮助!

Thank you,

谢谢你！

Evan

埃文

2 个解决方案

#1

You could just Google "php get from database," and use some normal array pre-processing, but I worry the advice you find may not be ideal. Here's what I'd do:

您可以使用谷歌“php从数据库获取”并使用一些常规的数组预处理，但是我担心您找到的建议可能并不理想。这就是我做的事情:

$pdo = new PDO('mysql:host=host;dbname=dbname', 'user', 'pass');
$result = $pdo->query(<<<SQL
   SELECT
      ID,
      Name
   FROM
      A
      -- Alternative to `JOIN B USING(ID)` or `JOIN B ON (A.ID = B.ID)
      NATURAL JOIN B
SQL
);
$a = array();
while ($row = $result->fetch()) {
   if (!isset($a[$row['ID']]) {
      $a[$row['ID']] = array();
   }
   $a[$row['ID']][] = $row['Name'];
}

You could also GROUP BY the ID and GROUP_CONCAT the names to be exploded in PHP later to skip the manual array creation and reduce some iteration (although SQL will do more in that case).

您还可以通过ID和GROUP_CONCAT来分组，以便在PHP中发生爆炸，从而跳过手动数组创建并减少一些迭代(尽管SQL将在这种情况下做更多的工作)。

#2

Adding a simple ORDER BY A.ID to the SQL query would probably get you quite far in "grouping" the items together. It's difficult to give a more detailed answer without knowing exactly what you want to do with the "groups".

添加一个简单的ORDER BY a。对SQL查询的ID可能会使您在“分组”这些项时走得很远。如果你不知道你想用“群体”做什么，很难给出更详细的答案。

#1