How to merge select 2 same name param in full outer join-CodePudding

I have 2 tables that have the same trial_id field name. With full outer join, I want to get trial_id from each separate table. But I want to merge it as one trial_id. What should I do?

SELECT ht.trial_id as trial_id, cts.trial_id AS trial_id, ht.subject, cts.trial_contract_name 
FROM
    hubspot_ticket ht 
FULL OUTER JOIN cs_trial_sheet cts 
        ON cts.trial_id = ht.trial_id

Expected result: Only one trial_id column and it must not be null. If ht.trial_id -> get cts.trial_id.

CodePudding user response：

You can use COALESCE() according to MySQL's Join Documentation:

The single result column that replaces two common columns is defined using the coalesce operation. That is, for two t1.a and t2.a the resulting single join column a is defined as a = COALESCE(t1.a, t2.a), where:

COALESCE(x, y) = (CASE WHEN x IS NOT NULL THEN x ELSE y END)

If the join operation is any other join, the result columns of the join consist of the concatenation of all columns of the joined tables.

Try this instead:

select ht.subject, cts.trial_contract_name,
COALESCE(ht.trial_id, cts.trial_id) AS trial_id
FROM
    hubspot_ticket ht 
FULL OUTER JOIN cs_trial_sheet cts 
        ON cts.trial_id = ht.trial_id

But, I don't think MySQL does have FULL OUTER JOIN, I usually use LEFT JOIN, UNION, RIGHT JOIN as an alternative.

CodePudding user response：

select COALESCE(ht.trial_id, cts.trial_id) as trial_id