php - MySQL: Complete missing data with parent tables -

let's assume have 3 tables in database, representing hierarchic order:

countries, states, , cities

each city connected 1 state, each state 1 country. simple represent in database.

let's further assume each of tables contains field tax_rate. in basic case tax rate defined on country level , null on other levels. however, overwritten on of levels below.

when query city node tax rate. defined right within same node, more defined on of next-higher levels.

what efficient way accomplish either in mysql or on php level? in real life application there not 1 such field many of them.

below simple database schema of example. of course have foreign key definitions.

create table `countries` (   `id` int(11) unsigned not null auto_increment,   `tax_rate` float(4,2) default null,   `name` varchar(20) default null,   primary key (`id`) );  insert `countries` (`id`, `tax_rate`, `name`) values     (1,8.00,'switzerland'),     (2,16.00,'germany');   create table `cities` (   `id` int(11) unsigned not null auto_increment,   `state_id` int(11) default null,   `tax_rate` float(4,2) default null,   `name` varchar(20) default null,   primary key (`id`) )  nsert `cities` (`id`, `state_id`, `tax_rate`, `name`) values     (1,1,null,'bern'),     (2,1,null,'zollikofen'),     (3,2,null,'zurich'),     (4,2,5.30,'wettingen'),     (5,2,null,'winterthur'),     (6,2,6.60,'handligen'),     (7,3,null,'bayern-town 1'),     (8,3,null,'bayern-town 2'),     (9,3,9.00,'bayern-town 3');   create table `states` (   `id` int(11) unsigned not null auto_increment,   `country_id` int(11) default null,   `tax_rate` float(4,2) default null,   `name` varchar(20) default null,   primary key (`id`) );  insert `states` (`id`, `country_id`, `tax_rate`, `name`) values     (1,1,null,'bern'),     (2,1,9.00,'zurich'),     (3,2,null,'bavaria'); 

this handled on either level: mysql or php

i'd prefer mysql approach:

select cities.name, coalesce(cities.tax_rate,states.tax_rate,countries.tax_rate) cities join states on cities.state_id=states.id join countries on states.country_id = countries.id 

this return city's tax rate if not null, else state's. if null, it'll return country's tax rate.