Query - Get Latest Revision from Several Tables

[JH0401]

EX: 6 tables (Table1, Table2 ... Table6)

All tables have the following columns:
ProjectID [String]
RevisionDescription [String]
RevisionDate [DateTime]

I want to use the RevisionDate column to determine
the last revision and the ProjectID column to determine
the project relationship.

Need to return return RevisionDescription & RevisionDate
of last revision where ProjectID = "Proj123"

Any help would be greatly appreciated... thanks!

 

[r937]

select t.ProjectID
     , t.RevisionDescription 
     , t.RevisionDate
  from Table1 as t
inner
  join ( 
       select ProjectID
            , max(RevisionDate) as maxdate
         from Table1
       group
           by ProjectID 
       ) as m
    on m.ProjectID = t.ProjectID
   and m.maxdate   = t.RevisionDate
 where t.ProjectID = 'Proj123'

this will work with and without the WHERE condition for a specific project

r937.com | rudy.ca

 

[JH0401]

I only want to return the last revision
across all tables...

 

[r937]

replace Table1 in both places with a derived table

select t.ProjectID
     , t.RevisionDescription 
     , t.RevisionDate
  from [red](
       select ProjectID
            , RevisionDescription 
            , RevisionDate
         from Table1
       union
       select ProjectID
            , RevisionDescription 
            , RevisionDate
         from Table2
       union
       select ProjectID
            , RevisionDescription 
            , RevisionDate
         from Table3
       union
       select ProjectID
            , RevisionDescription 
            , RevisionDate
         from Table4
       union
       select ProjectID
            , RevisionDescription 
            , RevisionDate
         from Table5
       union
       select ProjectID
            , RevisionDescription 
            , RevisionDate
         from Table6
       )[/red] as t

inner
  join ( 
       select ProjectID
            , max(RevisionDate) as maxdate
         from [red](                              
              select ProjectID                    
                   , RevisionDescription          
                   , RevisionDate                 
                from Table1                       
              union                               
              select ProjectID                    
                   , RevisionDescription          
                   , RevisionDate                 
                from Table2                       
              union                               
              select ProjectID                    
                   , RevisionDescription          
                   , RevisionDate                 
                from Table3                       
              union                               
              select ProjectID                    
                   , RevisionDescription          
                   , RevisionDate                 
                from Table4                       
              union                               
              select ProjectID                    
                   , RevisionDescription          
                   , RevisionDate                 
                from Table5                       
              union                               
              select ProjectID                    
                   , RevisionDescription          
                   , RevisionDate                 
                from Table6                       
              )[/red] as t2                        
       group
           by ProjectID 
       ) as m
    on m.ProjectID = t.ProjectID
   and m.maxdate   = t.RevisionDate
 where t.ProjectID = 'Proj123'

r937.com | rudy.ca

 

[JH0401]

What if the column names are slightly
different in the tables?

Table1 (ProjectID, RevisionName, RevDate)
Table2 (ProjectID, RevisionName, RevDate)
Table3 (ProjectID, RevisionDesc, RevisionDate)
Table4 (ProjectID, RevisionDesc, RevisionDate)
Table5 (ProjectID, RevisionDesc, RevisionDate)
Table6 (ProjectID, RevisionDesc, RevisionDate)

Thanks again!

 

[r937]

What if the column names are slightly
different in the tables?

then you would change the names used in the query




r937.com | rudy.ca

 

[JH0401]

Thank you... I'll give it a go and apply credit later!

 

[JH0401]

How does this work:

"select t.ProjectID
, t.RevisionDescription
, t.RevisionDate"

when untion contains fields
named something else in some
tables?

ex:

from (
select ProjectID
, RevisionDescription
, RevisionDate
from Table1
union
select ProjectID
, RevDesc
, RevDate
from Table2
) as t

do "t.RevisionDescription, t.RevisionDate" work
on both tables?

 

[r937]

when the tables have different columns, use aliases in the first subselect in the union...

select t.[b]PID[/b]         
     , t.[b]RevDescr[/b]    
     , t.[b]RevDate[/b]     
  from (
       select [blue]ProjectID[/blue]             as [b]PID[/b]
            , [blue]RevisionDescription[/blue]   as [b]RevDescr[/b] 
            , [blue]RevisionDate[/blue]          as [b]RevDate[/b]
         from Table1
       union
       select [red]Proj_id[/red]
            , [red]RevName[/red]
            , [red]DateLastRev[/red]
         from Table2
       ...

r937.com | rudy.ca

 

[JH0401]

...so we only need to specify the
aliases to match the column name
for the first subselect and as
long as each subsequent subselect
has the same number of columns
it will use the same alias?

 

[r937]

correct

that's exactly how UNION works -- it's in every tutorial

r937.com | rudy.ca

 

[JH0401]

Thanks again.