如何正确编写查询以使用单个表的两个副本?

Question

我需要显示一个属性，指的是同一属性的两个不同实例。

我有一组表，例如分支，地址，Property_for_rent，状态。我需要显示属性数据，包括branch_state和property_state。这两个属性来自表状态行名称。我的问题是我没有找到一种方法来两次引用两个不同的实例加入表状态。我已经尝试了完全联接，但 sql 无法识别我的表状态的一个副本，Union 不起作用，因为如果我将查询拆分为 ，我将没有相同数量的列。

需要联接以下两个查询：

SELECT p.property_no, p.prop_type, p.rooms, p.rent, s.name AS property_state, staff_no
FROM state s
JOIN address a ON s.state_id = a.state_id
JOIN property_for_rent p ON a.address_id = p.address_id
ORDER BY rent ASC;

---

+-------------+-----------+-------+------+----------------+----------+
| property_no | prop_type | rooms | rent | property_state | staff_no |
+-------------+-----------+-------+------+----------------+----------+
| PR200       | Flat      | 3     |   24 | Nevada         | SQ523    |
| PR901       | Flat      | 7     |   31 | Vermont        | SL569    |
| PR806       | House     | 3     |   54 | Minnesota      | NULL     |

---

SELECT branch_no, z.name AS branch_state
FROM branch b
JOIN address a ON b.address_id = a.address_id 
JOIN state z ON a.state_id = z.state_id;

---

+-----------+----------------+
| branch_no | branch_state   |
+-----------+----------------+
| B424      | Kentucky       |
| B947      | Massachusetts  |
| B942      | South Carolina |
| B714      | North Dakota   |

branch_state 和 property_state 是状态表中 name 属性的别名。

Answer 1

您需要以某种方式将分支连接到属性。 让我假设property_for_rent有一列用于branch_id：

SELECT p.property_no, p.prop_type, p.rooms, p.rent,
sp.name AS property_state, staff_no,
sb.name as branch_state
FROM property_for_rent p JOIN
address ap
ON ap.address_id = p.address_id JOIN
state sp
ON sp.state_id = ap.state_id JOIN
branch b
on p.branch_id = b.branch_id JOIN
address ab
ON ab.address_id = b.address_id JOIN
state sb
ON sb.state_id = ab.state_id
ORDER BY rent ASC;