Skip to content
Advertisement

Full outer join not giving the answer I need

I am using PostgreSQL and am having difficulty with getting a series of queries that combine the data from two tables (t1, t2)

t1 is

studyida gender age
a M 1
a M 2
a M 3
b F 4
b F 5
b F 6
c M 13
c M 14
c M 15

and t2 is

studyida studyidb gender age
a z M 3
a z M 4
a z M 5
NULL y F 7
NULL y F 8
NULL y F 9
c x M 10
c x M 11
c x M 12
NULL w F 7
NULL w F 8
NULL w F 9
NULL u M 7
NULL u M 8
NULL u M 9

t1 and t2 are related via StudyIDA and gender. What I need is a comprehensive listing from both tables, including including the ages. Sometimes the age in t1 equals the age in t2 (e.g. for StudyIDA=a, age=3) but most of the time it does not.

I am looking to create a table like this

StudyIDA StudyIDB gender ageA ageB
a z M 1
a z M 2
a z M 3 3
a z M 4
a z M 5
b NULL F 4
b NULL F 5
b NULL F 6
NULL y F 7
NULL y F 8
NULL y F 9
c x F 13
c x F 14
c x F 15
c x F 10
c x F 11
c x F 12
NULL w F 7
NULL w F 8
NULL w F 9
NULL u M 7
NULL u M 8
NULL u M 9

I was thinking that first a full outer join of t1 and t2 would give me what I want but it does not.

Then I was thinking I need a listing of all the individuals (lets call it t3), and then do a series of inserts (e.g. t1+t3 and also t1+t3) into a new table to ‘construct’ what I need. I am really stuck on the odd times when age in t1 equals the age in t2 (e.g. for StudyIDA=a, age=3).

I am still not getting what I need. Here is my code so far

Advertisement

Answer

A full join that includes the age maybe?
And some coalesce’s for common fields.

studyida | studyidb | gender | agea | ageb
:------- | :------- | :----- | ---: | ---:
a        | null     | M      |    1 | null
a        | null     | M      |    2 | null
a        | z        | M      |    3 |    3
a        | z        | M      | null |    4
a        | z        | M      | null |    5
b        | null     | F      |    4 | null
b        | null     | F      |    5 | null
b        | null     | F      |    6 | null
c        | null     | M      |   13 | null
c        | null     | M      |   14 | null
c        | null     | M      |   15 | null
c        | x        | M      | null |   10
c        | x        | M      | null |   11
c        | x        | M      | null |   12
null     | w        | F      | null |    7
null     | y        | F      | null |    7
null     | w        | F      | null |    8
null     | y        | F      | null |    8
null     | w        | F      | null |    9
null     | y        | F      | null |    9
null     | u        | M      | null |    7
null     | u        | M      | null |    8
null     | u        | M      | null |    9

db<>fiddle here

User contributions licensed under: CC BY-SA
2 People found this is helpful
Advertisement