1

I need to make a join between two tables. I need to count employees in each diversity group - it might be women, it might be POC etc. If it is women, I need to join on gender, if it's another group, i need to join on the group name.

TableA is a cte, where I have diversity_group, gender, employee_id, female_flag, additional_filtering_column.

Here's what I tried (without grouping yet):

SELECT 
    src_tbl.diversity_group
    tableA.employee_id
FROM 
    src_tbl
LEFT JOIN
    tableA
ON 1=1
    AND
        (CASE 
            WHEN src_tbl.diversity_group = 'Woman' THEN  tableA.GENDER = 'Woman'
            ELSE (1=1
                 AND src_tbl.diversity_group   = tableA.diversity_group 
                 AND tableA.additional_filtering_column = 1
                 )
        END)

I literally got all nulls, not even one match. And there should be matches.

Another version of what I've tried (I produced a female flag in cte)

AND
    (CASE
        WHEN src_tbl.diversity_group = 'Woman' AND tableA.FEMALE_FLAG = 1 THEN 1
        ELSE 0
    END) = 1
AND
    (CASE
        WHEN src_tbl.diversity_group = tableA.diversity_group AND tableA.additional_filtering_column = 1 THEN 1
        ELSE 0
    END) = 1

Same results.

I would appreciate any help.

jarlh
  • 42,561
  • 8
  • 45
  • 63
osfor
  • 17
  • 4
  • 3
    Tip: Skip the case expressions, use regular AND/OR instead. – jarlh Apr 18 '23 at 07:10
  • Do not use `case` *expressions* to return booleans because conditions in `case` *expressions* are also booleans. It's hard to logically distinguish conditions from result values (you may compose it in the way you think at design time, but it will be hard to read later by another person or future you: why this was placed in the `when` branch but not `then` for example). Combine all conditions in `when` with the corresponding `then` values with `AND` predicate and different `when` branches with `OR` predicate – astentx Apr 18 '23 at 07:45
  • Your first SQL is syntactically invalid for Teradata - CASE cannot return a boolean - so it returns nothing (except a syntax error message). But a bunch of "OR'ed" conditions in the ON clause may not be a good idea. If you must do "conditional join" instead of separate SQL statements, then write each join individually and UNION the results. And no need for (left) outer join if you are just counting. – Fred Apr 18 '23 at 15:47

0 Answers0