How do I join multiple tables to get a result set that includes rows that fulfil group by and having clause as well as rows that satisfy one criterion

I have two tables - input:

Input_table

Xref_table:

I want to join this tables on followingconditions

1. Input column names should match with pattern of keywords_primary and keywords_secondary having a separator followed or preceding by ex: ssn_no (ssn is primary and no is secondary)
2. Input column names should match with pattern of keywords_primary and if keywords_primary is replaced with '' in input column it should not have any other alphabets.

I have this query :

SELECT input.ait_no,
       input.schema_nm,
       input.table_nm,
       input.column_nm,
       xref.entity_category
FROM   input_table input
       INNER JOIN xref_table xref
               ON ( ( ( input.column_nm LIKE
                        '%[^a-zA-Z]' + xref.keywords_primary
                        + '[^a-zA-Z]%' )
                       OR ( input.column_nm LIKE xref.keywords_primary +
                                                 '[^a-zA-Z]%' )
                    )
                    AND ( ( input.column_nm LIKE
                            '%[^a-zA-Z]' + xref.keywords_secondary
                            + '[^a-zA-Z]%' )
                           OR ( input.column_nm LIKE xref.keywords_secondary +
                                                     '[^a-zA-Z]%' )
                           OR ( input.column_nm LIKE
                                '%[^a-zA-Z]' + xref.keywords_secondary ) ) )
                   OR ( REPLACE(input.column_nm, xref.keywords_primary, '') NOT
                        LIKE
                        '%[a-zA-Z]%' )
GROUP  BY input.ait_no,
          input.schema_nm,
          input.table_nm,
          input.column_nm,
          xref.entity_category
HAVING NOT Max(xref.excld_sw) = 1 

here all the results because of this join condition ( REPLACE(input.column_nm,xref.keywords_primary,'') not like '%[a-zA-Z]%') are not returned because i am using only primary_keyword and there are many keywords_secondary are assigned to it and excld_sw is set to 1 so they were eliminated ex: ( ssn_1 is matched but not returned because primary _keyword, secondary keyword 'ssn' and 'is' set to 1).

here how can i get the result set that fulfil group by and having clause as well as rows that satisfy ( REPLACE(input.column_nm,xref.keywords_primary,'') not like '%[a-zA-Z]%') condition while joining even though group by and having didn't not fulfilled.

Output_table

Create a temp table and try it

CREATE TEMPORARY TABLE temp_join_results AS
SELECT input.ait_no,
       input.schema_nm,
       input.table_nm,
       input.column_nm,
       xref.entity_category
FROM   input_table input
       INNER JOIN xref_table xref
               ON ( ( ( input.column_nm LIKE
                        '%[^a-zA-Z]' + xref.keywords_primary
                        + '[^a-zA-Z]%' )
                       OR ( input.column_nm LIKE xref.keywords_primary +
                                                 '[^a-zA-Z]%' )
                    )
                    AND ( ( input.column_nm LIKE
                            '%[^a-zA-Z]' + xref.keywords_secondary
                            + '[^a-zA-Z]%' )
                           OR ( input.column_nm LIKE xref.keywords_secondary +
                                                     '[^a-zA-Z]%' )
                           OR ( input.column_nm LIKE
                                '%[^a-zA-Z]' + xref.keywords_secondary ) ) )
                   OR ( REPLACE(input.column_nm, xref.keywords_primary, '') NOT
                        LIKE
                        '%[a-zA-Z]%' ));

-- Select results from the temporary table that fulfill the HAVING clause
SELECT tjr.ait_no,
       tjr.schema_nm,
       tjr.table_nm,
       tjr.column_nm,
       tjr.entity_category
FROM temp_join_results tjr
WHERE tjr.ait_no IN (
    SELECT tjr_inner.ait_no
    FROM temp_join_results tjr_inner
    GROUP BY tjr_inner.ait_no
    HAVING NOT MAX(tjr_inner.excld_sw) = 1
);