Data model

[07mishin]

Hello everyone. Experts of Sense, please tell me how to optimally assemble the model?

I have 3 tables. Below description.

1 table [Plans]: contains plan numbers (data density - 100%).

plan numbers

111

222

2 table [Procedures]: contains procedure numbers (data density - 100%) and plan numbers (may not be filled in).

procedure numbers	plan numbers
1	111
2

3 table [Contracts]: contains procedure numbers (may not be filled in) and plan numbers (may not be filled in).

procedure numbers	plan numbers
1	111
2
	333

Task conditions:

1) collect all 3 tables into one resulting table

2) all data from the tables "Plans" and "Contracts" should be loaded in the resulting table

3) data from the "Procedures" table should be limited to data from the "Plans" table.

[MarcoWedel]

please also post your expected resulting table

[07mishin]

For a better understanding, I added a field to the contracts.

[Plans]:

plan numbers

111

222

333

[Procedures]:

plan numbers	procedure numbers
111	1
	2
	3

[Contracts]: 

plan numbers	procedure numbers	contract numbers
111	1	555
	2	777
333		888
		999

Expected resulting table: (all data from the tables "Plans" and "Contracts") and  data from the "Procedures" table should be limited to data from the "Plans" table.

procedure numbers	plan numbers	contract numbers
1	111	555
2	222	777
	333	888
		999

[MarcoWedel]

how is the association between plan number 222 and procedure number 2  derived from your sample data?

[07mishin]

Sorry, I must have been mistaken.

Correct result table:

plan numbers	procedure numbers	contract numbers
111	1	555
222
	2	777
333		888
		999