Advanced join three tables Postgresql

I've got three tables, one containing transport moves between storage and usage location, it has a "from" and "to" uuid, and an 'amount' column. The UUIDs in the "from" and "to" columns refer to a row in one of two tables either the 'storage_location' table or the 'usage_location' table. Now I don't know in which of these two tables the UUID is going to be found

Now I'm trying to transport moves table to either the 'storage_location' table or the 'usage_location' table, so I can match the name and coordinates columns to a transport move. Resulting in something like: amount, to_name, to_coordinates, from_name, from_coordinates

A main problem is that the 'to' and 'from' information can both be in the same 'usage_location' or 'storage_location' tables, but then still need to change the column name based on if it matches the "to" or "from" uuid to either "to_name" or "from_name"

Does anyone have any tips about naming these condionally, or if there's a better way to do this alltogether?

The tables look like:

CREATE TABLE transport (
    amount double precision NOT NULL,
    timestamp_start timestamp without time zone,
    timestamp_end timestamp without time zone,
    "to" uuid,
    "from" uuid,
    type_water character varying
);

CREATE TABLE location_storage (
    type character varying NOT NULL,
    uuid uuid NOT NULL,
    coordinates double precision[],
    status character varying,
    name character varying NOT NULL,
    content_latest double precision,
);

CREATE TABLE location_usage (
    name character varying NOT NULL,
    type character varying,
    coordinates double precision[],
    uuid uuid,
    description text,
    status character varying
);

The desired result would be:

Transport table: to (uuid), amount, to_name (either from usage location table or storage location table), to_coordinates: (either from usage location table or storage location table) from (uuid), from_name (either from usage location table or storage location table), to_coordinates: (either from usage location table or storage location table)

I've tried things like:

SELECT amount,distance,timestamp_start,type_water,
location_storage.name as to_name, location_storage.type as to_type, location_storage.coordinates as to_coordinates, location_storage.description as to_description, location_storage.status as to_status,
location_usage.name as from_name, location_usage.type as from_type, location_usage.coordinates as from_coordinates,  location_usage.status as from_status
FROM transport
INNER JOIN location_storage ON transport.to = location_storage .uuid OR transport.from = location_storage .uuid
LEFT OUTER JOIN location_usage ON transport.to = location_usage.uuid OR transport.from = location_usage.uuid

but this doesn't work if both the transport "from" and "to" uuids are both located in the same location table

Example Datasets transport:

amount	timestamp_start	timestamp_end	to	from	type_water
15.1	2024-02-16	2024-02-16	UUID_1	UUID_2	Dummy

location_storage:

type	uuid	coordinates	status	name	content_latest
Type1	UUID_1	12323	Status1	Name1	Content_1

location_usage:

name	type	coordinates	uuid	description	status
Name2	Type2	62562	UUID_2	Desc_2	Status_2

Required Result:

amount	distance	timestamp_start	type_water	to_name	to_type	to_coordinates	to_description	to_status	from_name	from_type	from_coordinates	from_status
tbc	tbc	tbc	tbc	tbc	tbc	tbc	tbc	tbc	tbc	tbc	tbc	tbc

Solution

Union the twp location tables and then join transport to that union twice.

Try something like this:

with locations as (
  select uuid, coordinates, name, 'storage' as location_type
    from location_storage
  union all
  select uuid, coordinates, name, 'usage' as location_type
    from location_usage
)
select dst.uuid as to_uuid, t.amount, dst.name as to_name, 
       dst.coordinates as to_coordinates, src.uuid as from_uuid,
       src.name as from_name, src.coordinates as from_coordinates
  from transport t
       join locations src on src.uuid = t."from"
       join locations dst on dst.uuid = t."to";