Cascading SELECT statement in MySQL

Question

I have a table laid out like below, where name is a branch name, and each branch can have a parent, which references back to the id column in the same table. In this example 'Liverpool', 'Manchester' and 'Chester' are all children of 'North West', which itself is a child of 'Master'.

id  name               parent_id
1   Master             0
2   North West         1
3   Liverpool          2
4   Manchester         2
5   Chester            2

I then have a branchSettings table, which looks like this:

id  branch_id  settingKey    settingValue
1   1          waitTime      5
2   1          timeOutTime   10
3   2          waitTime      7
4   5          waitTime      15

Branch 1 ('Master') will always have every setting as it's the master, and every child should inherit these settings, but each branch can override the default setting if they wish. For example, 'North West' (id 2) has overwritten 'waitTime' from 5 to 7, which should cascade to 'Liverpool', 'Manchester' and 'Chester', but 'Chester' (id 5) has decided that 'waitTime' should be 15 - overriding both 'North West' and 'Master'.

Rather than code this out in PHP, is there a simple way to cascade a SELECT command, whereby I can say ... Select every setting from the table where branch_id = 5 and have that return every Master setting where no override exists, but then the override values where they do? So that it returns:

id  branch_id  settingKey    settingValue
2   5          timeOutTime   10
3   5          waitTime      15

Thanks

Answer 1

This is a bit long for a comment.

You question is: "Is there a simple way to cascade a SELECT command?"

The answer to your question is a simple "no" -- at least until MySQL 8.0. You are storing hierarchical data. SQL (in general) offers mechanisms for handling such data. MySQL does not currently support any of them.

If your hierarchy is not too deep, just doing the work in PHP is probably the simplest solution. Otherwise, you might want to consider alternative ways of structuring the information. Here is a good place to get started.