Joining tables problem

[#1]

Hi Dear Qv Community

I have this four tables

Tab1

ID	Field A
1	A
1	B
2	C
2	D
3	M
3	N

Tab2

ID	Field B
1	10
1	100
2	20
2	3
3	10
3	5

Tab3

ID	Field C
1	Mat
1	Col
2	Cuc
2	XTZ
3	Abz
3	MNS

Tab4

ID	Field D
1	0,5
1	0,6
2	0,33
2	0,22
3	0,11
3	0,35

I Want obtain this Finally Table

ID	Field A	Field B	Field C	Field D
1	A	10	Mat	0,5
1	B	100	Col	0,6
2	C	20	Cuc	0,33
2	D	3	XTZ	0,22
3	M	10	Abz	0,11
3	N	5	MNS	0,35

I try several ways but could not achieve the goal, this is the result after concatenante tables

ID	Field A	Field B	Field C	Field D
1	A
1	B
1		10
1		100
1			Mat
1			Col
1				0,5
1				0,6
2	C
2	D
2		20
2		3
2			Cuc
2			XTZ
2				0,33
2				0,22
3	M
3	N
3		10
2		5
2			Abz
2			MNS
2				0,11
2				0,35

[#2 · Accepted solution]

Your data model is assuming that ID is a primary key but it is not.  That's why any type of join with this data source will not work.

Your primary key seems to be ID and field A.  but that link is not in all tables.

However it does look like the load order dictates the join. so you could do this.

JoinedTable:

Load RowNo() as RowID, ID, Field A resident Tab1;

join

load RowNo() as RowID, Field B resident Tab2;

join

load RowNo() as RowID, Field C resident Tab3;

join

load RowNo() as RowID, Field D resident Tab4;

drop field RowID;

[#3]

Thanks work finw for me