php - MySQL 连接，从一个表返回 1 行，并从另一个表返回多行作为数组或列表

Question

我正在开展一个对笔记本电脑进行编目的项目，因此我试图重复使用尽可能多的信息。 MySQL 表的简化版本是:

Table: laptop
|----------------------------------|
| id | make | line | model         |
| 1  | 1    | 1    | Late 2015 13" | 
|----------------------------------|

Table: make 
|----------------------------------|
| id | name  | other info          |
| 1  | Apple |                     |
|----------------------------------|

Table: line
|----------------------------------|
| id | name        | other info    |
| 1  | MacBook Pro |               |
|----------------------------------|

Table: networking
|----------------------------------|
| id | name         | other info   |
| 1  | A wifi card  |              |
| 2  | Another card |              |
| 3  | Yet another  |              |
|----------------------------------|

Table: laptop_networking 
|----------------------------------|
| id | networking | laptop         |
| 1  | 3          | 1              |
| 2  | 1          | 1              |
|----------------------------------|

到目前为止，我使用当前语句在 PHP 中检索数据

$statement = $dbc->prepare("
SELECT l.id
     , m.id AS makeID
     , m.name AS makeName
     , n.id AS lineID
     , n.name AS lineName
     , l.model
  FROM laptop l
  JOIN make m
    ON l.make = m.id
  JOIN line n
    ON l.line = n.id
 WHERE l.id = :laptop);
$statement->bindParam(':laptop', $anID, PDO::PARAM_INT);
$statement->execute();
$theLaptop = $statement0>fetch();

目前使用 $anID = 1 运行此代码会返回

|---------------------------------------------------------------|
| id | makeID | makeName | lineID | lineName    | Model         |
| 1  | 1      | Apple    | 1      | MacBook Pro | Late 2015 13" | 
|---------------------------------------------------------------|

我想要做的是将另一列附加到表中，该表返回来自 Networking 的所有名称，这些名称的 ID 等于laptop_networking 中的一行，其中笔记本电脑字段等于检索到的笔记本电脑行中的 ID

如:

|------------------------------------------------------------------------------------------|
| id | makeID | makeName | lineID | lineName    | model         | networking               |
| 1  | 1      | Apple    | 1      | MacBook Pro | Late 2015 13" | Yet another, A wifi card |
|------------------------------------------------------------------------------------------|

这可能吗，因为我对不同类型的 JOIN 的多次尝试都没有产生预期的结果。

谢谢

Answer 1

尝试这个查询:

SELECT laptop.id,
       make.id AS makeID,
       make.name AS makeName,
       line.id AS lineID,
       line.name AS lineName,
       laptop.model,
       t.networking
FROM laptop
INNER JOIN make
    ON laptop.make = make.id
INNER JOIN line
    ON laptop.line = line.id
INNER JOIN
(
    SELECT t1.laptop, GROUP_CONCAT(t2.name) AS networking
    FROM laptop_networking t1
    INNER JOIN networking t2
        ON t1.networking = t2.id
    GROUP BY t1.laptop
) t
    ON laptop.id = t.laptop
WHERE laptop.id = :laptop

此处演示:

Rextester