how to do one join to a table, Instead 2 joins on this table

Question

I need values City name and country name which is on Location table. Below, I reached these values with using 2 joins on same table

But How to do it with one join instead 2 joins on same table?

I need to learn efficient solution?

select cus.*, l.name As 'City Name', lo.name As 'CountryName' 
from Customer cus
left JOIN Location l ON l.id= cus.cityId
left JOIN Location lo ON lo.id= cus.countryId

Answer 1

It's an odd schema to have the location table host both cityID and CountryID values in the same column. I might prefer the two-join option as you have listed; however, if you are looking for another method, then you could use conditional aggregation with a cartesian join.

create table customer (
  id integer, 
  name varchar(10), 
  cityId varchar(10), 
  countryId varchar(10)
  );
  
create table location (
  id varchar(10), 
  name varchar(20)
  );
  
insert into customer values 
(1, 'John', 'ci101', 'co501'), 
(2, 'Sally', 'ci102', 'co502'), 
(3, 'Henry', 'ci103', 'co503');

insert into location values 
('ci101', 'Paris'), 
('ci102', 'Miami'), 
('co501', 'France'), 
('co502', 'United States'),
('co503', 'Spain');

---

select cus.id, 
 cus.name, 
 cus.cityId, 
 max(case when cus.cityId = l.id then l.name end) as city_name,
 cus.countryId,
 max(case when cus.countryId = l.id then l.name end) as country_name
from customer cus, location l
group by 1,2,3,5
order by 1,2;

id	name	cityid	city_name	countryid	country_name
1	John	ci101	Paris	co501	France
2	Sally	ci102	Miami	co502	United States
3	Henry	ci103		co503	Spain

---