0

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.

1
  • Use an outer join
    – Philᵀᴹ
    Commented Jan 16, 2014 at 22:11

1 Answer 1

2

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

3
  • CASE didn't work in this context on the query but it did work if I queried after this query if that makes sense.
    – LOSTinDB
    Commented Jan 23, 2014 at 19:14
  • @LOSTinDB Ah, yes, that does make sense... actually that's an oversight in my answer. It should have read END as regionname in the line right before FROM instead of just END. The original version would not have resulted in a valid column name, which we needed since you were using CREATE TABLE ... SELECT and the column names in the new table were being implicitly created from the results returned by the query. The answer has been updated. Commented Jan 23, 2014 at 22:29
  • Thanks for the update. Will try it out. I run tons of sub-reports daily from an LDAP feed to do employee data. This isn't touching the "original" null but is actually going to be used to run some exception reports. Obviously if the DB were updated in real time this would be a horrible way to do things but with one update per day for the whole company it works much smoother with a few nightly batch jobs. I create about 40 tables and 30 huge spreadsheets over the course of 15-20 mins each night vs. setting up views and bad performance on large queries.
    – LOSTinDB
    Commented Jan 24, 2014 at 1:00

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.