Comparing data from two tables and appending into 3rd table

[santal]

Hello All
Sorry i might be missing something in below problem I have 2 tables as below

Table 1
ID RID StartM EndM
1 xyz 1100 040.0935 040.1254
2 xyz 1100 029.0110 038.1003
3 xyz 1100 029.0110 038.1010

Table 2

RID StartM EndM XYZ 1100 029.0110 029.0143 XYZ 1100 029.0154 029.0176 XYZ 1100 029.0600 029.0638 XYZ 1100 035.0814 035.0858 XYZ 1100 038.0923 038.1010 XYZ 1100 040.1051 040.1053 XYZ 1100 040.1197 040.1229

Table3
RID StartM EndM ID

Now I need to create 3rd table based on above two tables
for table1.XYZ 1100 table1.StartM>Table2.StartM then table1.ID=2or 3 and
for table1.xyz 1100 table1.EndM< Table2.EndM then table1.ID=1
Regards

[June7]

Don't understand the criteria for record selection. What is the relationship between these tables? Do they have a common unique ID?

[santal]

RID in both tables are linked via 1—1

[June7]

So 'XYZ' is not real value? Show some real data. Can even provide db if you want. Follow instructions at bottom of my post.

[santal]

Hi
I have attached the db.

[June7]

Okay, really confused now. RID field is not a unique value in either table. Need better description of what you want. From the following sample data, what would be the desired output.

Table1

ID	RID	StartM	EndM
000000043508	AAV 1100	029.0110	038.1010
000000043510	AAV 1100	040.0935	040.1254
000000043509	AAV 1100	029.0110	038.1003
000002037326	AAV 1700	029.0550	029.0638
000000043512	AAV 2100	040.0935	040.1254
000000043511	AAV 2100	029.0550	038.1003
000000043513	AAV 2800	028.1441	029.0154
000000043514	AAV 2900	028.1518	028.1661
000000043515	AAV 3102	029.0095	029.0550
000000043516	AAV 3103	028.1452	029.0215
000000043517	AAV 3400	038.1003	040.0935
000002037329	AAV 3700	035.0816	035.0878
000000043518	AAV 3800	028.1562	028.1628

Table2

RID	StartM	EndM
AAV 1100	29.011	29.0143
AAV 1100	40.1197	40.1229
AAV 1100	29.0154	29.0176
AAV 1100	38.0923	38.101
AAV 1100	29.06	29.0638
AAV 1100	35.0814	35.0858
AAV 1100	40.1051	40.1053
AAV 1700	40.0998	40.1051
AAV 1700	29.055	29.0594
AAV 1700	29.0594	29.0638
AAV 1700	40.0945	40.0998
AAV 1900	40.0945	40.1051
AAV 2100	40.1165	40.1197
AAV 2100	40.1197	40.1229
AAV 2100	40.1165	40.1197
AAV 2100	40.1165	40.1197
AAV 2100	40.1165	40.1197
AAV 2100	35.0839	35.088
AAV 2100	40.0945	40.1003
AAV 2100	38.0948	38.101
AAV 2100	29.055	29.0594
AAV 2800	28.1562	28.159
AAV 2800	28.159	28.1661
AAV 2900	28.159	28.1661
AAV 3102	29.0095	29.0132
AAV 3102	29.0154	29.0215
AAV 3103	28.1452	28.1496
AAV 3103	28.1584	28.1628
AAV 3103	29.0154	29.0215
AAV 3400	38.101	38.1012
AAV 3400	40.0943	40.0945
AAV 3700	35.0847	35.0878
AAV 3700	35.0816	35.0847
AAV 3800	28.1562	28.159
AAV 3800	28.159	28.1628