Search code examples
sqlpostgresqlinner-join

SQL help joining tables

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?

Solution

  • 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.