英文:
SQL help joining tables
问题
我有一个表格
资产表
资产编号 | 资产名称 | 资产类型 | 产品编号
转移表
转移编号 | 从资产编号 | 到资产编号 | 产品编号
我试图连接这些表格以获取以下结果
转移编号 | 从资产名称 | 到资产名称 | 产品编号
我使用了以下查询语句
选择 *
从 转移
左连接 资产 on 资产编号 = 从资产编号 或 资产编号 = 到资产编号
但是我得到的输出中，从资产名称和到资产名称在不同的行上。我希望它们在同一列而不是行中。
有人可以帮助我，我应该如何查询以获得我想要的输出？
英文:
I have a table
Assets table
Asset Id | Asset Name | Asset Type | Product Id
Transfers Table
Transfer Id | From Asset ID | To Asset Id | Product Id
I'm trying to join these table to get me
Transfer ID | From Asset Name | To Asset Name | Product Id
I used
Select *
From Transfer
Left join Asset on asset.id = from_asset_id or asset.id = to_asset_id
But I'm getting the output in different rows for the from asset name and to asset name.I want it to be in a same column not rows.
Can anyone help me, how should I query to get to my output?
答案1
得分: 2
你似乎想要进行两个连接，如下所示：
从 transfers t
内连接 assets a1，条件为 a1.id = t.from_asset_id
内连接 assets a2，条件为 a2.id = t.to_asset_id
我不确定为什么要在这里使用“左连接” - 除非两个资产列是可空的。
英文:
You seem to want two joins, as in:
select t.id,
a1.name from_asset_name,
a2.name to_asset_name,
t.product_id
from transfers t
inner join assets a1 on a1.id = t.from_asset_id
inner join assets a2 on a2.id = t.to_asset_id
I am not sure why you would use left join here - unless the two asset columns are nullable.
答案2
得分: 0
我尝试了一种略有不同标记法的方法，但您也可以使用以下方法：
select t1.transfer_id, t2.asset_name as from_asset_name,
t3.asset_name as to_asset_name, t1.product_id from transfers as t1
inner join assets as t2 on t1.from_asset_id=t2.asset_id
inner join assets as t3 on t1.to_asset_id=t3.asset_id;
英文:
I made an attempt using slightly different notation from that of the accepted answer, but you could also use the following:
select t1.transfer_id, t2.asset_name as from_asset_name,
t3.asset_name as to_asset_name, t1.product_id from transfers as t1
inner join assets as t2 on t1.from_asset_id=t2.asset_id
inner join assets as t3 on t1.to_asset_id=t3.asset_id;
答案3
得分: 0
另一种方法是通过以下方式进行：
SELECT
t.transfer_id,
a1.asset_name AS from_asset_name,
a2.asset_name AS to_asset_name,
t.product_id
FROM
transfers t
INNER JOIN
assets a1 ON a1.asset_id = t.from_asset_id
INNER JOIN
assets a2 ON a2.asset_id = t.to_asset_id;
此查询的结果将包括转账ID、对应的转出资产名称、对应的转入资产名称和产品ID，所有这些信息都在每个转账的同一行中。
英文:
an other way to do it is by:
SELECT
t.transfer_id,
a1.asset_name AS from_asset_name,
a2.asset_name AS to_asset_name,
t.product_id
FROM
transfers t
INNER JOIN
assets a1 ON a1.asset_id = t.from_asset_id
INNER JOIN
assets a2 ON a2.asset_id = t.to_asset_id;
the result of this query will include the transfer ID, the corresponding from asset name, the corresponding to asset name, and the product ID, all in the same row for each transfer.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论