我有三张桌子
类型

    +----+-------+
    | id | type  |
    +----+-------+
    |  1 | typeA |
    |  2 | typeB |
    |  3 | typeC |
    +----+-------+

品牌(包含有母品牌标识的品牌和子品牌,如brandC是brand a的子品牌)
+----+--------+--------+
| id | brand  | parent |
+----+--------+--------+
|  1 | brandA |     0  |
|  2 | brandB |     0  |
|  3 | brandC |     1  |
+----+--------+--------+

设备
+----+-------+-------+
| id | type  | brand |
+----+-------+-------+
|  1 |     1 |     2 |
|  2 |     2 |     1 |
|  3 |     3 |     3 |
+----+-------+-------+

我写了这个问题:
$query = "select
    a.id,
    b.type,
    c.brand

from
    equipment a
        join type b
            on a.type=b.id
        join brand c
            on a.brand=c.id
where
    a.id=3";

结果如下:
+----+--------+---------+
| id | type   | brand   |
+----+--------+---------+
|  3 | typeC  | brandC  |
+----+--------+---------+

如果某个品牌有父品牌,我应该如何修改查询以显示父品牌。例如brandC是brand a的一个子品牌。所以我的结果应该是:
+----+--------+---------+----------------+
| id | type   | brand   | Parent Brand   |
+----+--------+---------+----------------+
|  3 | typeC  | brandC  | brandA         |
+----+--------+---------+----------------+

如果没有父品牌,则单元格为空
另外,我将如何修改上述查询,以查看所有设备及其品牌和子品牌如下。
+----+--------+---------+----------------+
| id | type   | brand   | Parent Brand   |
+----+--------+---------+----------------+
|  1 | typeA  | brandB  |                |
|  2 | typeB  | brandA  |                |
|  3 | typeC  | brandC  | brandA         |
+----+--------+---------+----------------+

最佳答案

因为不是所有品牌都有有效的父项,所以需要父项的左外联接:

select e.id, t.type, b.brand, bp.brand as parentBrand
from equipment e join
     type t
     on e.type= t.id join
     brand b
     on e.brand = b.id left outer join
     brand bp
     on b.parent = bp.id
where e.id = 3;

我还将别名更改为表名的缩写。这使得查询更容易执行。

关于php - 如何使用两个联接编写此查询,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/18403931/

10-14 14:00
查看更多