ISPrincess
Programmer
I have 3 Tables.
Table1
Table2
Table3
Some of the fields that I need to return into a recordset are contained in Table1, some are contained in table3. The link between table1 and table3 is contained in table2.
Table1
Field1
Field2
Table2
Field1
Field3
Table3
Field3
Field4
So Table1 uses Field1 to link to Table2 which in turn uses Field3 to link to Table3. I would then need to return data from say Table1.Field1, Table1.Field2, Table3.Field3, Table3.Field4 (although in reality I am returning appx 10 fields all together)
I only want the data from the LAST dated record in Table1 along with its corresponding info from table3.
So Table1.field1 can have multiple records (because this is like an audit table)
Field1 = 1
Field2 = 20020101
Field1 = 1
Field2 = 20021228
etc...
Field1 = 2
Field2 = 20020501
Field1 = 2
Field2 = 20020628
I only want the latest one record for each (1 and 2) and then collect the data from table3 that correspondes.
I am not sure if this even makes any sense. The only way I am thinking that this could work is to select distinct field1 ... with criteria, then using that call another s/p to collect the rest of the field data necessary.
To make matters even worse - this needs to go across 3 linked servers.
Table1
Table2
Table3
Some of the fields that I need to return into a recordset are contained in Table1, some are contained in table3. The link between table1 and table3 is contained in table2.
Table1
Field1
Field2
Table2
Field1
Field3
Table3
Field3
Field4
So Table1 uses Field1 to link to Table2 which in turn uses Field3 to link to Table3. I would then need to return data from say Table1.Field1, Table1.Field2, Table3.Field3, Table3.Field4 (although in reality I am returning appx 10 fields all together)
I only want the data from the LAST dated record in Table1 along with its corresponding info from table3.
So Table1.field1 can have multiple records (because this is like an audit table)
Field1 = 1
Field2 = 20020101
Field1 = 1
Field2 = 20021228
etc...
Field1 = 2
Field2 = 20020501
Field1 = 2
Field2 = 20020628
I only want the latest one record for each (1 and 2) and then collect the data from table3 that correspondes.
I am not sure if this even makes any sense. The only way I am thinking that this could work is to select distinct field1 ... with criteria, then using that call another s/p to collect the rest of the field data necessary.
To make matters even worse - this needs to go across 3 linked servers.