Potato_head Potato_head - 4 years ago 121
MySQL Question

mysql return default row if no row is found

I have a table structure like this:

+----+-----------+--------+
| id | attr | value |
+----+-----------+--------+
| 1 | attr1 | val1 |
| 2 | attr1 | val2 |
| 2 | default | val3 |
| 3 | default | val4 |
+----+-----------+--------+


Here, (id, attr) is the primary key. Also, id (int), attr (varchar), value (varchar).

I want to design a query such that for all distinct values of id I can fetch value of a specific attribute, and if it is not present for that attribute but is present as default value then return that default value.
i_e result above table, for attr1 will be

+----+--------+
| id | value |
+----+--------+
| 1 | val1 |
| 2 | val2 |
| 3 | val4 |
+----+--------+

Answer Source
DROP TABLE IF EXISTS my_table;

CREATE TABLE my_table
(id INT NOT NULL, attr VARCHAR(12) NOT NULL,value  VARCHAR(12) NOT NULL,PRIMARY KEY(id,attr));

INSERT INTO my_table VALUES
(1 ,'attr1','val1'),
(2 ,'attr1','val2'),
(2 ,'default','val3'),
(3 ,'default','val4');

SELECT DISTINCT a.id
              , COALESCE(b.value,a.value) value
           FROM my_table a 
           LEFT 
           JOIN my_table b 
             ON b.id = a.id 
            AND b.attr = 'attr1';

+----+-------+
| id | value |
+----+-------+
|  1 | val1  |
|  2 | val2  |
|  3 | val4  |
+----+-------+
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download