|
|
|||||||||
|
|||||||||
| |||||||||
|
|
|
| |||||||||
![]() |
|
|
«
Previous Thread
|
Next Thread
»
|
Thread Tools | Search this Thread | Rate Thread | Display Modes |
|
|
|
Get inside! Sample the range of functionality easily built with JMSL Library for Time Series Data Analysis, Heat Maps, Portfolio Optimization, Monte Carlo Simulation, Stock Price Charting and more. Download Now! |
|
#1
|
|||
|
|||
|
PLS, help
,I am trying to JOIN two tables together with the LEFT OUTER JOIN statement, but with a twist :1. table 1 id and table 2 projectid are the join field 2. Table 1 has de detail info for several ocurrences in table 2. For each id in table 1 (project detail), there are many entries of projectid on table 2 (update log on project) 3. I need a query that gets each project detail (table 1) with ONLY the LAST (most recent) update date and comment (table 2) for that project. The result report will look something like this: Repeat_____________________________________________ PROJECT NAME(table 1) PROJECT DATE(table 1) PROJECT OWNER(table 1) LAST UPDATE(table 2) LAST UPDATE COMMENT(table 2) Repeat______________________________________________ I have used the SELECT TOP 1 function, the DISTINCT function, I am out of ideas .Thanx |
|
#2
|
|||
|
|||
|
Code:
select * from table1 t1 join table2 t2 on t1.id = t2.projectid where updateDate = (select max(updateDate) from table2 where projectid = t2.projectid) |
|
#3
|
|||
|
|||
|
Great Thanx!,One more thing...how about if I want to display also T1 date for T2 that do not have any updateDate but I need then to be displayed as 'NA', Can this be accomplished?
Thanx in advance |
|
#4
|
|||
|
|||
|
Using union is one possibility
Code:
select t1.*,t2.* from table1 t1 join table2 t2 on t1.id = t2.projectid where updateDate = (select max(updateDate) from table2 where projectid = t2.projectid) union select t1.*, '',... 'n/a', ... from table1 t1 where id not in ( select projectid from table2) The number of columns in each part of the union must be the same so you need to add as many constants as there are columns in table2 to the second select. These must be the same type as the columns in table2. |
![]() |
| Viewing: Dev Shed Forums > Databases > MS SQL Development > Joining Tables With Twist |
| Thread Tools | Search this Thread |
| Display Modes | Rate This Thread |
|
|
|
|