How can I set a field when original lookup is NULL or isn't in the list?

Question

I am trying to merge our HR data. I had to create my own tables to give a "regions" table

 DROP TABLE
IF EXISTS testgiver.ldap_karen;

CREATE TABLE testgiver.ldap_karen (uid VARCHAR (10) NOT NULL PRIMARY KEY) SELECT
  ldap_full.thomsnamedisplay,
    ldap_full.uid as 'uid',
    ldap_full.businesscategory,
    ldap_full.mail,
    ldap_full.givenname,
    ldap_full.sn,
    ldap_full.departmentnumber,
    ldap_full.title,
    ldap_full.thomsmgdescr,
    ldap_full.thomsmgdescroverride,
    ldap_full.thomscompanydescr,
    ldap_full.thomscompanydescroverride,
    ldap_full.thomslocationstreet1,
    ldap_full.thomslocationcity,
    ldap_full.thomslocationstatedescr,
    ldap_full.thomslocationpostal,
    ldap_full.thomslocationcountrydescr,
    ldap_full.thomssupervisorid,
    ldap_full.thomssupervisoriddescr,
    ldap_full.thomscompanyshortdescroverride,
    ldap_full.thomsbulevel1descr,
    ldap_full.thomsbulevel1shortdescr,
    ldap_full.thomsbulevel2descr,
    ldap_full.thomsbulevel2shortdescr,
    ldap_full.thomsroleindicatordescr,
    ldap_full.thomsjobcodedescr,
    ldap_full.thomsjobfamilycode,
    ldap_full.thomsjobfamilydescr,
    ldap_full.thomsrecordtypedescr,
    sup_email.mail AS supemail, 
  reports_regionnames.regionname
FROM
    ldap_full
LEFT JOIN ldap_full AS sup_email ON ldap_full.thomssupervisorid = sup_email.uid
LEFT JOIN reports_regions ON ldap_full.thomslocationcountrydescr = reports_regions.thomslocationcountrydescr
LEFT JOIN reports_regionnames ON reports_regions.thomsregion1 = reports_regionnames.regiondid

This works fine and adds a region to each user. However I would like to take it a step further. I would like it to handle instances where and employee does not have ldap_full.thomslocationcountrydescr (so null) or if the employees ldap_full.thomslocationcountrydescr is not in the lookup table. This seems simple but I have tried a few things and I don't think my syntax is even close.

Would simply like the region to be set to "bad" for each employee with a "new country" or no country.

Answer 1

Arguably, you would be better off with the NULL since that's the absence of a value, rather than arbitrary text indicating the absence of a valid value... but:

    -- 8< -- snip -- 8<-- 
    ldap_full.thomsjobfamilycode,
    ldap_full.thomsjobfamilydescr,
    ldap_full.thomsrecordtypedescr,
    sup_email.mail AS supemail, 
    CASE WHEN ldap_full.thomslocationcountrydescr IS NULL 
         THEN 'hey null source data here'
         WHEN reports_regionnames.regionname IS NULL AND ldap_full.thomslocationcountrydescr IS NOT NULL 
         THEN 'my lookup table needs something'
         ELSE reports_regionnames.regionname END AS regionname /* << updated from comments below */
FROM
    ldap_full
    -- 8< -- snip -- 8<-- 

The last column selected will be the output of the CASE expression.

When ldap_full.thomslocationcountrydescr IS NULL then we return one string... otherwise when we didn't match the lookup table, yet ldap_full.thomslocationcountrydescr isn't null, we return a different string... otherwise (ELSE) we return the original value, from reports_regionnames.regionname.

The first valid match wins.

http://dev.mysql.com/doc/refman/5.6/en/control-flow-functions.html