带有附加列的SQL联接查询

Question

我有以下Server表：

资产表

+---------+-----------+------------+
| AssetID | AssetName | LocationID |
+---------+-----------+------------+

定位表

+-------------+---------------+------------------+
|  LocationID |  LocationName |  SubLocationName |
+-------------+---------------+------------------+

如果资产表LocationID = LocationName但同时附加了相应的LocationName和SubLocationName作为额外列，我如何返回结果？

资产表

+---------+-----------+------------+
| AssetID | AssetName | LocationID |
+---------+-----------+------------+
|       1 | Asset1    |        123 |
+---------+-----------+------------+

定位表

+------------+--------------------+----------------------+
| LocationID | LocationName       | SubLocationName      |
+------------+--------------------+----------------------+
|        123 | Area1              | Sub1                 |
+------------+--------------------+----------------------+

将返回：

+--------+-------+------+
| Asset1 | Area1 | Sub1 |
+--------+-------+------+

谢谢保罗。

Answer 1

    SELECT a.AssetID,a.AssetName,l.LocationName, l.SubLocationName
    FROM   Assets a
    JOIN   Location l ON a.LocationId = l.LocationId

Answer 2

您需要加入表并查询您感兴趣的列：

SELECT AssetName, LocationName, SubLocationName
FROM   Assets a
JOIN   Location l ON a.LocationId = l.LocationId