SQL Merge Tables Based on Present Values

Question

I am trying to populate a table with the consent values for two different places based on the same ID.

So I have table A:

id    consent_value
111      Y
222      Y
333      N

and table B:

 id    consent_value
111         Y
222         N
444         N
555         N

my desired outcome table would look like this:

id      a_consent_value       b_consent_value
111          Y                     Y
222          Y                     N
333          N                    NULL
444         NULL                   N
555         NULL                   N

I have been trying to wrap my head around how to do the SQL for this, but am really struggling with creating my desired outcome table.

Could anyone point me in the right direction?

Answer 1

A full outer join should do the trick:

SELECT          COALESCE(a.id, b.id) AS id,
                a.consent_value AS a_consent_value,
                b.consent_value AS b_consent_value
FROM            a
FULL OUTER JOIN b ON a.id = b.id