tonyjasta tonyjasta - 3 months ago 8
SQL Question

How to replace id's in table with representation of objects with these id's in another table?

Two tables:

old.address:
id | country | city | street_address
1 | Ukraine | Kyiv | Bandery 26
2 | Poland | Warsaw | Kyrva 28

new.users:
id | name | address1 | address2
1 | Dohn | 1 | 2
2 | Kahn | 2 | 1


need to set Dohn addresses as
Ukraine, Kyiv, Bandery 26
instead of 1

Answer

This will work on sql-server, MySQL, MariaDB, PostgreSQL

select u.name, 
concat(a1.country,', ',a1.city,', ',a1.street_address) as address1,
concat(a2.country,', ',a2.city,', ',a2.street_address) as address2
from new.users u
left join old.address a1 on (u.address1 = a1.id)
left join old.address a2 on (u.address2 = a2.id)
where u.name = 'Dohn';

On Oracle you'll have to concatenate differently

select u.name, 
a1.country ||', '|| a1.city ||' ,'|| a1.street_address as address1,
concat(concat(concat(concat(a1.country,', '),a1.city),' ,') a1.street_address) as address2
from new.users u
left join old.address a1 on (u.address1 = a1.id)
left join old.address a2 on (u.address2 = a2.id)
where u.name = 'Dohn';

MySql and MariaDB also have the CONCAT_WS function.

SQLite also has the double pipes like Oracle. And a printf function:

select u.name, 
(a1.country ||', '|| a1.city ||' ,'|| a1.street_address) as address1,
printf('%s, %s, %s', a2.country, a2.city, a2.street_address) as address2
from new.users u
left join old.address a1 on (u.address1 = a1.id)
left join old.address a2 on (u.address2 = a2.id)
where u.name = 'Dohn';