How to return all records in table when join table has missing records

Question

So I think I am overthinking this again and can not seem to come down to a solution. Plus, I think my auto increment does not fully work in the database. Anywho, I am trying to join 2 tables, and use a "trimmed" column to match the other column and retrieve 2 columns... Sorry if that was very confusing. Here is my attempt:

PS... IT works more or less, however when the condition is null, it does not return anything.... how do I make it so that it still comes back? The condition is still met in the other table, ie the workd_id is 11... but I had to add it manually separately. Thoughts? Thank you!

INSERT INTO permits.JOB_CATEGORY(jc_desc, WORK_TYPE_workd_id)
    SELECT DISTINCT 
        JOB_CATEGORY, workd_id
    FROM edmonton_upto_10may2016_mostly_text
    JOIN WORK_TYPE
    ON WORK_TYPE = CONCAT('(',work_num,')',' ',work_desc)
    GROUP BY JOB_CATEGORY;

Here is the data from table WORK_TYPE

workd_id  workd_desc                  work_num
    1     New                             01
    2     Interior Alterations            03
    3     Demolition                      99
    4     Exterior Alterations            03
    5     Deck Attached                   03
    6     Building - New                  01
    7     Add Suites to Single Dwelling   07
    8     Addition                        02
    9     Foundation                      04
    10    Hot Tub                         14
    11      
    12    Structure                       05
    13    Move Building on to Site        12
    14    Move Building OnSite            12
    15    Add Suites to Multi-Dwelling    08
    16    Convert Non-Res to Residential  09
    17    Swimming Pool                   14
    18    Remove Suites                   11
    19    Convert Residential to Non-Res  10
    20    Remove Suite(s)                 11
    21    Move on Mobile Home             12
    22    Move Building OFF Site          98
    23    Attached Garage/Carport         15
    24    Excavation                      04
    25    Footing & Foundation            04
    26    New House                       01
    27    Structural Frame                05
    28    231                             23

and here is the table edmonton_upto_10may2016_mostly_text after

SELECT DISTINCT JOB_CATEGORY, WORK_TYPE FROM edmonton_upto_10may2016_mostly_text;

 JOB_CATEGORY                                WORK_TYPE
Accessory Building Combination               (01) New
Other Miscellaneous Building                 (03) Interior Alterations
Commercial Demolition                        (99) Demolition
Other Miscellaneous Building                 (03) Exterior Alterations
Uncovered Deck Combination                   (03) Deck Attached
Rowhousing & Semi-Detachment                 (01) Building - New
Other Miscellaneous Building                 (99) Demolition
Other Miscellaneous Building                 (07) Add Suites to Single Dwelling
House Combination                            (01) New
Commercial Final                             (03) Interior Alterations
Commercial Final                             (03) Exterior Alterations
Commercial Final                             (02) Addition
Commercial Footing / Foundation              (04) Foundation
Other Miscellaneous Building                 (02) Addition
Commercial Final                             (01) New
Other Miscellaneous Building                 (14) Hot Tub
Commercial Final    
Other Miscellaneous Building                 (01) Building - New
Other Miscellaneous Building                 (03) Deck Attached
Commercial Structural Framing                (05) Structure
Other Miscellaneous Building                 (12) Move Building on to Site
Commercial Final                             (12) Move Building OnSite
Commercial Final                             (08) Add Suites to Multi-Dwelling
Mobile Home Move On 
Commercial Final                             (09) Convert Non-Res to Residential
Other Miscellaneous Building                 (14) Swimming Pool
Other Miscellaneous Building                 (11) Remove Suites
Commercial Final                             (10) Convert Residential to Non-Res
Other Miscellaneous Building                 (04) Foundation
Commercial Excavation                        (04) Foundation
Commercial Final                             (11) Remove Suite(s)
Uncovered Deck Combination                   (03) Exterior Alterations
Commercial Final                             (99) Demolition
Other Miscellaneous Building                 (12) Move on Mobile Home
Other Miscellaneous Building                 (98) Move Building OFF Site
Other Miscellaneous Building                 (15) Attached Garage/Carport
Commercial Final                             (04) Excavation
Other Miscellaneous Building                 (08) Add Suites to Multi-Dwelling
Commercial Final                             (04) Footing & Foundation
Rowhousing & Semi-Detachment                 (01) New House
Rowhousing & Semi-Detachment                 (04) Foundation
Commercial Final                             (05) Structural Frame
Uncovered Deck Combination                   (02) Addition
Commercial Final                              231
Other Miscellaneous Building                 (01) New House

SO...

What didn't populate, was when the WORK_TYPE was NULL. Is there a way to still have it populate, even if it was null?

After running the Query, I got:

JOB-CATEGORY                      workd_id
Accessory Building Combination     1
Commercial Demolition              3
Commercial Excavation              9
Commercial Final                   1
Commercial Footing / Foundation    9
Commercial Structural Framing      12
House Combination                  1
Other Miscellaneous Building       2
Rowhousing & Semi-Detachment       6
Uncovered Deck Combination         4

and Mobile Home Move On did not pop in the Job_Category because

ON WORK_TYPE = CONCAT('(',work_num,')',' ',work_desc)

came up as a NULL value.... but the null in work_type table had an ID...

Thank you! Trying to understand this as well as I go

Answer 1

To answer your question: You can achieve your goal by using left outer join instead of join as Android Addict mentioned.

Another problem you could encounter in the future could be that the concat function results in NULL if any of the inputs of the function is NULL. What you can do is use the ifnull function inside the concat function.

The main problem, however, I see in the database scheme. It is generaly not a good aproach to have column WORK_TYPE defined as you have it, because it is not atomical. So even the first normal form is broken and the result is, that you need to solve problems that comes with that. You should split the column WORK_TYPE into two columns as is in table WORK_TYPE. (The naming convention is apparently also not realy good). OR mayby a better aproach would be not to have the work_number in table edmonton_upto_10may2016_mostly_text at all because it does not seem that you need it duplicated there and you can always join this value.

Answer 2

I think you're looking for an outer join:

INSERT INTO permits.JOB_CATEGORY(jc_desc, WORK_TYPE_workd_id)
    SELECT DISTINCT 
        JOB_CATEGORY, workd_id
    FROM edmonton_upto_10may2016_mostly_text
    LEFT OUTER JOIN WORK_TYPE
    ON WORK_TYPE = CONCAT('(',work_num,')',' ',work_desc)
    GROUP BY JOB_CATEGORY;

that way your primary table (left side in this example - edmonton_upto_10may2016_mostly_text) is always returned, regardless of a matching record on right side table WORK_TYPE