Merging multiple tables sharing a column

Question

I have a list of tables like this:

t1

ID | Name
3  | 'AAA'
4  | 'BBB'
5  | 'CCC'
6  | 'DDD'
7  | 'EEE'

t2

ID | Password
3  | 'test'
6  | 'password'

t3

ID | Birth Year | Last Name
4  | 1990 | 'John'
6  | 1988 | 'Megan'
7  |   -  | 'Bob'

t4

ID | Birth Year
7  |   1985

I want to merge them all into this, noticing that t3 and t4 both have birth year columns, but the value will only be in either one.

ID | Name  | Password | Birth Year | Last Name
3  | 'AAA' | 'test'   |      -     |      -
4  | 'BBB' |    -     |     1990   | 'John'
5  | 'CCC' |    -     |      -     |      -
6  | 'DDD' |'password'|     1988   | 'Megan'
7  | 'EEE' |    -     |     1985   | 'Bob'

Does anyone know how this can be done? t1 is the "master" table, so it will always contain all the IDs.

I've tried:

select * \
from t1 \
LEFT outer join t2 on t1.ID = t2.ID \
LEFT outer join t3 on t1.ID = t3.ID \
LEFT outer join t4 on t1.ID = t4.ID

But it doesnt work properly, it has separate columns for each individual columns in t1, t2, t3, t4

You need OUTER JOIN. Something like: t1 LEFT OUTER JOIN t2 LEFT OUTER JOIN t3 LEFT OUTER JOIN t4. — Al Kepp, Oct 19 '17 at 22:06
See this: https://stackoverflow.com/questions/38549/what-is-the-difference-between-inner-join-and-outer-join — Al Kepp, Oct 19 '17 at 22:08
In your `SELECT` statement you need to think of what columns you actually want: `t1.ID, t1.Name, t2.Password, COALESCE(t3.[Birth Year], t4.[Birth Year]), t3.[Last Name]`, I would combine it with `GROUP BY`and `WHERE` clause because `t1` is the main table so you don't want rows that do not exist in `t1`. — Moseleyi, Oct 19 '17 at 22:20
You have SELECT *, that's why you have all those columns. Write properly, what particular columns you want like t1.ID, t2.Password etc. — Al Kepp, Oct 19 '17 at 22:28

Noxthron · Answer 1 · 2017-10-20T20:32:17.540

0

you can selectively left join t4 to t1, then show t4.BirthYear if it is not null, otherwise t3.BirthYear. like this:

SELECT t1.ID, t1.Name, t2.Password, COALESCE(t4.BirthYear, t3.BirthYear) as BirthYear, t3,LastName
FROM t1
 LEFT OUTER JOIN t2 on t1.ID = t2.ID
 LEFT OUTER JOIN t3 on t1.ID = t3.ID
 LEFT OUTER JOIN t4 on t1.ID = t4.ID and t4.BirthYear is NOT NULL

edited Oct 20 '17 at 20:32

answered Oct 19 '17 at 22:36

Noxthron

492
3
7

... you've got a typo (one of the `t4.BirthYear` references should probably be `t3`), but the bigger problem is your join to `t4`, which most likely doesn't warrant the additional condition. – Clockwork-Muse Oct 20 '17 at 16:38
yes you are right, second one should be t3 because if the t4 join condition have been met then it means t4.BirthYear has a value. So the first argument to Coalesce should be t4.BirthYear. With this extra explanation t4 join should not be a problem. – Noxthron Oct 20 '17 at 20:33

score 0 · Answer 2 · answered Oct 19 '17 at 22:45

0

select t1.ID,
t1.Name,
t2.Password,
COALESCE(t3.[Birth Year],t4.[Birth Year]), t3.[Last Name]
from t1 
LEFT outer join t2 on t1.ID = t2.ID 
LEFT outer join t3 on t1.ID = t3.ID 
LEFT outer join t4 on t1.ID = t4.ID

answered Oct 19 '17 at 22:45

Buddhi

416
4
14

Merging multiple tables sharing a column

2 Answers2