[SOLVED] How to read more specific configurations in two dimensions which only have been overwritten partially

I am trying to read configuration entries from a database which has a base "project" and a base "machine" defined by the ID 0. The base configuration (key + value) can be overwritten by more specific projects and machines. I'm using HeidiSQL on MariaDB.

project_id	machine_id	Key	Value	Interpretation
0	0	Uc_max	5000	Machine 2 and Project 2
1	0	Uc_max	4000	Machine 2 and Project 1
0	1	Uc_max	10000	Machine 1 and Project 2
1	1	Uc_max	15000	Machine 1 and Project 1
0	2	Uc_max	20000	Machine 2 and Project 1

edit1:

The interpretation column shows only one possible way of interpretation. The idea of the table is to define only those more specifc values, which are needed. All combinations, which are not specified, should default to the base project / machine. So e.g. the entry for project_id 0 and machine_id 0 will also be valid for project_id 7 and machine_id 9. The last entry (project_id 0 and machine_id 2) is valid for machine 2 and all projects.

I have started with the following minimal example table:

DROP TABLE IF EXISTS `overwrite`;
CREATE TABLE IF NOT EXISTS `overwrite` (
  `id` int(11) NOT NULL,
  `project_id` int(11) NOT NULL,
  `machine_id` int(11) NOT NULL,
  `key` char(50) NOT NULL DEFAULT '',
  `value` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

DELETE FROM `overwrite`;
INSERT INTO `overwrite` (`id`, `project_id`, `machine_id`, `key`, `value`) VALUES
    (0, 0, 0, 'Uc_max', 5000),
    (1, 1, 0, 'Uc_max', 4000),
    (2, 0, 1, 'Uc_max', 10000),
    (3, 1, 1, 'Uc_max', 15000),
    (4, 0, 2, 'Uc_max', 20000);

With my current query I can read the first four entries correctly. Unfortunately, when I try to read the configuration for project 1 and machine 2 no entry is being shown. As no entry for project 1 exists, it should default to 0 and return the entry with ID 4.

SELECT o.`id`, o.`project_id`, o.`machine_id`, o.`key`, o.`value`
FROM overwrite o
LEFT JOIN (
     -- get all keys, for specific project_id
    SELECT DISTINCT o1.`key`
    FROM overwrite o1
    -- insert specific project_id here
     WHERE o1.`project_id` = 1
) subquery_project ON o.`key` = subquery_project.`key`
LEFT JOIN (
    -- Get all keys for specific machine_id
    SELECT DISTINCT o1.`key`
    FROM overwrite o1
    -- insert specific machine_id here
    WHERE o1.`machine_id` = 2
) subquery_machine ON o.`key` = subquery_machine.`key`
-- always read entries for 0, replace second ID with specifc project / machine
WHERE o.`project_id` IN (0, 1)
AND o.`machine_id` IN (0, 2)
AND (
    -- If project_id is 0, include only if there is no more specific projects entry
    (o.`project_id` = 0 AND subquery_project.`key` IS NULL)
    -- Always include more specific project entries
    OR o.`project_id` = 1
)
AND (
    -- If machine_id is 0, include only if there is no more specific machine entry
    (o.`machine_id` = 0 AND subquery_machine.`key` IS NULL)
    -- Always include more specific machine entries
    OR o.`machine_id` = 2
)

I understand, that subquery_project.key will NOT be null in this case and causes the issue. Still I cannot figure out how to modify the query or create a different one, which works fine.

edit2: I want to save and retrieve configurations for a specific project but also want to consider global configurations (project_id = 0) only when there's no override for the specific project. That way, only overrides and the global configuration get saved to the database. Keys for which no specific override exists, will have the values of the global configuration (project_id = 0). This got more complex as not only a global configuration exists per project, but also per machine.

Please give me feedback, if more information is needed on the concept of overriding and how those overrides are stored in the database.

My problem is, that I cannot retrieve the correct configuration values in all cases. The SELECT from above should return line with ID 4, but doesn't return anything. If I adjust it to find the valid configurations for project 7 and machine 1 it returns correctly the line with ID 2:

SELECT o.`id`, o.`project_id`, o.`machine_id`, o.`key`, o.`value`
FROM overwrite o
LEFT JOIN (
     -- get all keys, for specific project_id
    SELECT DISTINCT o1.`key`
    FROM overwrite o1
    -- insert specific project_id here
     WHERE o1.`project_id` = 7
) subquery_project ON o.`key` = subquery_project.`key`
LEFT JOIN (
    -- Get all keys for specific machine_id
    SELECT DISTINCT o1.`key`
    FROM overwrite o1
    -- insert specific machine_id here
    WHERE o1.`machine_id` = 1
) subquery_machine ON o.`key` = subquery_machine.`key`
-- always read entries for 0, replace second ID with specifc project / machine
WHERE o.`project_id` IN (0, 7)
AND o.`machine_id` IN (0, 1)
AND (
    -- If project_id is 0, include only if there is no more specific projects entry
    (o.`project_id` = 0 AND subquery_project.`key` IS NULL)
    -- Always include more specific project entries
    OR o.`project_id` = 7
)
AND (
    -- If machine_id is 0, include only if there is no more specific machine entry
    (o.`machine_id` = 0 AND subquery_machine.`key` IS NULL)
    -- Always include more specific machine entries
    OR o.`machine_id` = 1
)

edit3: updated the values in the SQL table to the same as the table to avoid confusion.

edit 4: As @ValNik has figured out, I wasn't aware that the result may be ambiguously. Therefore, the decision was made, that the project is always more important than the machine.