Returning One Record from Historical Table(s)

[ISPrincess]

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.

 

[Sqlsa]

Hi,
I think you can try using a subquery.
Select t1.f1, t1.f2,
t2.f1, t2.f3,
t3.f3, t3.f4 from t1
inner join t2 on t1.f1=t2.f1
inner join t3 on t2.f3=t3.f3
Where t1.f2 =
(select max(A1.f2) from t1 A1 where A1.f1=t1.f1)

Since you said all three tables are related, i assumed that key columns or linking cliumns don't allow null values. So I used Inner Joins.

I see this as a straight subquery.

Hope it works.

Thanks,

Sreenivas
---------

 

[ISPrincess]

Good morning SqlSa -
This is very interesting, obviously you are very experienced.

I have just one problem with this. Although I vaguely understand what you are doing, it would help if you could clarify what A1 is in your query. Is this a type? If so can you retype the query for me.

Thank you very very much for your time!

 

[ISPrincess]

Sorry,

It is early. A1 is the alias for T1.

I try it, and I get this error in Query Anal.


An aggregate may not appear in the WHERE clause unless it is in a subquery contained in a HAVING clause or a select list, and the column being aggregated is an outer reference.


Can someone help me with this?

 

[tlbroadbent]

Use the IN clause to check if the value is in a Select List created by the sub-query. Of course, the list will only contain one value for each f1.

Select t1.f1, t1.f2,
t2.f1, t2.f3,
t3.f3, t3.f4 from t1
inner join t2 on t1.f1=t2.f1
inner join t3 on t2.f3=t3.f3
Where t1.f2 IN
(Select max(A1.f2) From t1 A1 Where A1.f1=t1.f1)


You can also use a JOIN to a derived table or sub-query.

Select
t1.f1, t1,f2, t1.f4, t1.f7,
t3.f3, t3.f4, t3.f8
from t1
inner join t2
on t1.f1=t2.f1
inner join t3
on t2.f3=t3.f3
inner join
(Select f1, f2=max(f2)
From t1
Group By f1) As q1
On t1.f1=q1.f1
And t1.f2=q1.f2 Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.