I need to select same column name twice having deferent values based on another table-CodePudding

I created 2 tables 1st table "Storages" noting that Id is PK

2nd table "Transactions" where fields names are (Id, Source, Qty, Destination) Also noting that Id is PK

My SQL statement gave me nothing as follow

Select Storages.Name as 'From', Storages.Name as 'To'
from Storages,
     Transactions
where Storages.Id = Transactions.Source
  and Storages.Id = Transactions.Destination

I need to display the result even it is not distinct

CodePudding user response：

You can get your expected result using two JOIN, one on the source of the transaction and one on the destination:

SELECT s1.name AS source, s2.name AS destination
FROM transactions t 
JOIN storages s1 ON s1.id = t.source
JOIN storages s2 ON s2.id = t.destination;

In case you don't want the same combination of source and destination multiple times, use DISTINCT to show them only once:

SELECT DISTINCT s1.name AS source, s2.name AS destination
FROM transactions t 
JOIN storages s1 ON s1.id = t.source
JOIN storages s2 ON s2.id = t.destination;

Please have a look on SQL tutorials or documentations how JOIN works because this is one of the most important things when writing SQL queries.

A last note: Your description says you want to name your columns in the result "From" and "To". I recommend to avoid this because "FROM" is a SQL key word, so you can't just use it as column name. Therefore, I named them "Source" and "Destination". If you want to use "From" and "To" anyway, you can use quotes:

SELECT DISTINCT s1.name AS "From", s2.name AS "To"
FROM transactions t 
JOIN storages s1 ON s1.id = t.source
JOIN storages s2 ON s2.id = t.destination;