Join Multiple Tables from Different Months

Question

Given that we have 3 tables in MS Access,

table1

ID  MyName  Months
111 A         1
222 B         1
333 F         2
444 D         2
555 G         3
666 C         3

table2

ID  Income  Months
111 500       1
222 900       1
333 800       2
444 6000      2

table3

ID  Income  Months
555 65656   3
666 77777   3

I want to join table 2 and 3 into table 1 so that my return will be Table_Result

ID  NAME    Income  Months
111 A       500        1
222 B       900        1
333 F       800        2
444 D      6000        2
555 G     65656        3
666 C     77777        3

How do get the Table_Result?

possible duplicate of [Table-less UNION query in MS Access (Jet/ACE)](http://stackoverflow.com/questions/7933518/table-less-union-query-in-ms-access-jet-ace) — Vidya Sagar, Jun 30 '15 at 09:20

Raging Bull · Answer 1 · 2015-06-30T09:42:03.550

1

You don't have to use UNION when you can simply use LEFT JOIN with Nz:

SELECT T1.ID,T1.MyName as Name, Nz(T2.Income,T3.Income) as Income,T1.Months
FROM Table1 T1 LEFT JOIN
     Table2 T2 ON T1.ID=T2.ID LEFT JOIN
     Table3 T3 ON T1.ID=T3.ID
ORDER BY T1.ID

Explanation:

Nz function returns the second argument if first argument is null. Read more here.

Result:

ID  MyName  Income  Months
111 A       500     1
222 B       900     1
333 F       800     2
444 D       6000    2
555 G       65656   3
666 C       77777   3

Sample result in mysql SQL fiddle (used coalesce for mysql instead of Nz)

edited Jun 30 '15 at 09:42

answered Jun 30 '15 at 09:24

Raging Bull

18,593
13
50
55

Downvoter, Care to explain? My query will get exactly what OP wants (checked and verified). – Raging Bull Jun 30 '15 at 09:26
Yeh this is a perfect solution, don't understand downvotes on this – Matt Jun 30 '15 at 09:27
@Matt: Thanks mate, I don't know who downvoted it. I posted my answer after checking it works. – Raging Bull Jun 30 '15 at 09:29
There are no constraints posted by OP so its safe to assume that t2 and t3 can contain same id's, in which case neither this or @Matt solution is valid, union is the solution – Mladen Oršolić Jun 30 '15 at 09:31
@MladenOršolić: Your query only uses Table2 and Table3. Then why did OP add Table1 in his question if there are no relations between those **3** tables? And how do we get the name field? Your comment doesn't make sense. – Raging Bull Jun 30 '15 at 09:36
It doesnt make sense for ppl that didnt learn much more past simple joins..yea – Mladen Oršolić Jun 30 '15 at 10:05

score 0 · Answer 2 · answered Jun 30 '15 at 09:23

0

Try this query

select  a.id,a.Myname,b.income,a.month
from table1 a
join table2 b on a.id=b.id
union all
select  a.id,a.Myname,c.income,a.month
from table1 a
join table3 c on c.id=a.id

answered Jun 30 '15 at 09:23

Mukesh Kalgude

4,814
2
17
32

luk2302 · Answer 3 · 2015-06-30T09:37:35.140

You need to UNION table2 and table3 and then join them on table1:

SELECT table1.*, table4.income FROM table1 LEFT JOIN 
  (SELECT * FROM table2 UNION SELECT * FROM table3) as table4 
  ON table4.Months = table1.Months AND table4.ID = table1.ID

You first UNION both table2 and table3 which will just merge all their contents into one "table4" and the you just perform a JOIN on the matching Months and ID. You eventually might want to remove the condition for the Months if you dont actually need it.

Alternatively you could achieve the same result by performing (table1 JOIN table2) UNION (table1 JOIN table3)

score 0 · Answer 4 · answered Jun 30 '15 at 09:28

0

SELECT A.ID, A.MYNAME, A.MONTHS, B.INCOME
FROM Table1 AS A
INNER JOIN (SELECT * FROM Table2 UNION SELECT * FROM Table3) AS B ON B.ID = A.ID

answered Jun 30 '15 at 09:28

axxis

954
1
10
18

Mladen Oršolić · Answer 5 · 2015-06-30T09:51:21.770

You can use sql UNION operator to get single recordset from 2 or more tables

SELECT ID, Income, Months FROM table2
UNION
SELECT ID, Income, Months  FROM table3;

A solution to your question using UNION would then be :

Select t1.ID, t1.MyName, Table_Result.Income, t1.Months
  from Table1 t1
  join (SELECT ID, Income, Months FROM table2
        UNION
        SELECT ID, Income, Months  FROM table3) as Table_Result on Table_Result.id = t1.id;

And in case you want all (duplicate also) values you can use ALL modifier for UNION operator

Select t1.ID, t1.MyName, Table_Result.Income, t1.Months
  from Table1 t1
  join (SELECT ID, Income, Months FROM table2
        UNION ALL
        SELECT ID, Income, Months  FROM table3) as Table_Result on Table_Result.id = t1.id;

Here is an sql fiddle of the final example

It just took a while to edit it to contain all examples i wanted to provide. — Mladen Oršolić, Jun 30 '15 at 09:48

Join Multiple Tables from Different Months

5 Answers5