Question 191299 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – 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?

Tags:

2

Answers


  1. 0

    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.

    Login or Signup to reply.
  2. 0

    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;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search