我有三张桌子
类型
+----+-------+
| 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/