SQL Select inner join with multiple value

Question

How should I write the select statement with the following table? where ID can refer to either province or city.

CANADA Table:

canada_id	name	id	parent_id
1	Canada	33	0
2 (Province)	Alberta	1100	33
3 (Province)	British Columbia	1200	33
4 (city)	Banff	1101	1100
5 (city)	Calgary	1102	1100
6 (city)	Victory	1201	1200
7 (city)	Vancouver	1202	1200

CLIENT Table:

client_id	name	province_id	city_id
1	John	1100	1102
2	Kitty	1200	1201

I would like to return:

name	province_id	province_name	city_id	city_name
John	1100	Alberta	1102	Calgery

Answer 1

SELECT
    CLIENT.name,
    CLIENT.province_id, CANADA_province.name as province_name,
    CLIENT.city_id, CANADA_city.name as city_name
FROM CLIENT
    LEFT JOIN CANADA as CANADA_province
        ON CLIENT.province_id = CANADA_province.id
    LEFT JOIN CANADA as CANADA_city
        ON CLIENT.city_id = CANADA_city.id
WHERE CLIENT.name = 1;

If you want to get the "Full Client List", simply remove

WHERE CLIENT.name = 1;