How can I return a a value on no result in a MySQL View?-CodePudding

I have some MySQL records generated by a view:

 --------- ----------------------------- ------------ 
| user_id | meta_key                    | meta_value |
 --------- ----------------------------- ------------ 
|       2 | admin-first-timer-training  | NULL       |
|       2 | admin-first-timer-overnight | No         |
|       2 | admin-first-timer-outdoor   | No         |
 --------- ----------------------------- ------------

I want to have the records:

 --------- ----------------------------- ------------ 
| user_id | meta_key                    | meta_value |
 --------- ----------------------------- ------------ 
|       2 | admin-first-timer-training  | Yes        |
|       2 | admin-first-timer-overnight | No         |
|       2 | admin-first-timer-outdoor   | No         |
 --------- ----------------------------- ------------

Currently I'm generating these records with this view:

create VIEW `wp_record_db` AS 
select distinct `u`.`ID` AS `id`,
    (select distinct (
        CASE WHEN `wp_usermeta`.`meta_value`="No" THEN "No" 
              ELSE "Yes" END) 
    from `wp_usermeta` 
    where `wp_usermeta`.`user_id` = `u`.`ID` and `wp_usermeta`.`meta_key` in ('admin-first-timer-outside','admin-first-timer-outdoors','admin-first-timer-outdoor') 
    limit 1) AS `admin-first-timer-outdoor`,
    (select distinct (
        CASE WHEN `wp_usermeta`.`meta_value`="No" THEN "No" 
             WHEN `wp_usermeta`.`meta_value` IS NULL THEN "YES" 
             ELSE "Yes" END) 
    from `wp_usermeta` where `wp_usermeta`.`user_id` = `u`.`ID` and `wp_usermeta`.`meta_key` = 'admin-first-timer-training' 
    limit 1) AS `admin-first-timer-training`,
    (select distinct (
        CASE WHEN `wp_usermeta`.`meta_value`="No" THEN "No" 
             ELSE "Yes" END) 
    from `wp_usermeta` 
    where `wp_usermeta`.`user_id` = `u`.`ID` and `wp_usermeta`.`meta_key` = 'admin-first-timer-overnight' 
    limit 1) AS `admin-first-timer-overnight` 
from `wp_users` `u`;

As you can see in

(select distinct (CASE WHEN `wp_usermeta`.`meta_value`="No" THEN "No" WHEN `wp_usermeta`.`meta_value` IS NULL THEN "YES" ELSE "Yes" END) from `wp_usermeta` where `wp_usermeta`.`user_id` = `u`.`ID` and `wp_usermeta`.`meta_key` = 'admin-first-timer-training' limit 1) AS `admin-first-timer-training`,

I've been trying to test whether it's NULL using a Case statement, however that isn't providing the desired result.

SELECT IFNULL((select `wp_usermeta`.`meta_value` from `wp_usermeta` where `wp_usermeta`.`user_id` = 2 and `wp_usermeta`.`meta_key` = 'admin-first-timer-training'), "Yes");

Is as close as I've got - as this does seem to produce the desired return output, but I can't make it work in a Case statement - however I try and insert it, it always returns an Empty Statement.

What can I try?

CodePudding user response：

You can use conditional aggregation to make the query metter readable

But NULL will always be different from No so , you don't need extra checking for NULL

SELECT
    `user_id`,
    MIN(CASE WHEN `meta_key` = 'admin-first-timer-training' AND `meta_value` = 'No' THEn 'No' ELSE 'Yes' END) as 'admin-first-timer-training',
    MIN(CASE WHEN `meta_key` = 'admin-first-timer-overnight' AND `meta_value` = 'No' THEn 'No' ELSE 'Yes' END) as 'admin-first-timer-overnight',
   MIN(CASE WHEN `meta_key` = 'admin-first-timer-outdoor' AND `meta_value` = 'No' THEn 'No' ELSE 'Yes'  END) as 'admin-first-timer-outdoor'
FROM wp_usermeta
GROUP BY user_id

user_id | admin-first-timer-training | admin-first-timer-overnight | admin-first-timer-outdoor
------: | :------------------------- | :-------------------------- | :------------------------
      2 | Yes                        | No                          | No

db<>fiddle here