SQL Query to fetch final data for hierarchal configuration tables in a single query

Question

I have two tables i.e. config_attr and config. I am working with MySQL 8.0.32.

config_attr -> This table has attribute name and its default value. config -> This table has overriden or current value of any attribute which is present in config_attr for a particular client.

Below is a sample structure for both tables

CREATE TABLE config_attr (
   id INT,
   name VARCHAR,
   value VARCHAR,
   PRIMARY KEY (id)
);

CREATE TABLE config (
   id INT,
   clientId INT,
   configAttrId INT,
   value VARCHAR,
   PRIMARY KEY (id),
   FOREIGN KEY (configAttrId) REFERENCES config_attr(id)
);

Currently I am fetching the data separately using individual query and then using code to override default values.

However, I want to explore if there is any other way using which I can fetch the final overriden data in a single query.

Below is an example:

config_attr

id	name	value
1	n1	v1
2	n2	v2

config

configAttrId	clientId	value
1	10	v3

Query Output expected for client 10:

name	value
n1	v3
n2	v2

Answer 1

try coalesce with left join

SELECT 
    ca.name,
    COALESCE(c.value, ca.value) AS value
FROM 
    config_attr ca
LEFT JOIN 
    config c ON ca.id = c.configAttrId AND c.clientId = 10;