#1
  1. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2003
    Posts
    3
    Rep Power
    0

    Unhappy Joining Tables With Twist


    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. #2
  3. No Profile Picture
    Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Jan 2003
    Location
    Paris Uppland
    Posts
    2,370
    Rep Power
    391
    Code:
    select * from table1 t1 join table2 t2
    on t1.id = t2.projectid
    where updateDate = (select max(updateDate)
    from table2
    where projectid = t2.projectid)
  4. #3
  5. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2003
    Posts
    3
    Rep Power
    0
    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
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Jan 2003
    Location
    Paris Uppland
    Posts
    2,370
    Rep Power
    391
    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.

IMN logo majestic logo threadwatch logo seochat tools logo