WITH temp_table1
AS (
SELECT
DISTINCT client,
c_type,
0 AS IND
FROM
table1
UNION
SELECT
DISTINCT client,
c_type,
0 AS IND
FROM
table2
),
temp_all_types
AS (
SELECT
DISTINCT client,
( CASE
WHEN Upper(c_type) = 'NON-UC' THEN ind + 0
WHEN Upper(c_type) IN ( 'UC', 'UCMS', 'UCRB' )
THEN
ind + 1
END ) AS IND
FROM
temp_table1)
SELECT
client,
( CASE
WHEN ind = 0 THEN 'NON-UC'
ELSE 'UC'
END ) AS c_type
FROM
(
SELECT
client,
Sum(ind) AS IND
FROM
temp_all_types t
GROUP BY
client)R;