Take the 2-minute tour ×

I have a 3 table SQLServer Database.




When a Thing is ascribed to a Project an entry is put in the ProjectThingLink table. Things can move between Projects. The CreatedDate is used to know which Project a Thing was last moved too.

I am trying to create a list of all Projects with which Things are currently linked to them, but my brain is failing.

Is there an easy way of doing this?

share|improve this question
add comment

5 Answers

up vote 1 down vote accepted
select p.projectName, t.ThingName
from projects p
join projectThingLink l on l.projectId = p.projectId
join thing t on t.thingId = l.thingId
where l.createdDate =
( select max(l2.createdDate)
  from projectThingLink l2
  where l2.thingId = l.thingId

NOTE: Corrected after comment

share|improve this answer
I think there is a problem with this query. Won't it always show all things ever assigned to a project even if they've been reassigned? You need to remove the l2.projectid = l.projectid I think. –  Tom H. Dec 17 '08 at 15:52
Thanks, I have corrected it now. –  Tony Andrews Dec 17 '08 at 17:54
add comment

This will almost always give you better performance than the subquery method. You're basically looking for the row which doesn't have any other rows past it rather than looking for the row with the greatest date:

     dbo.Projects P
INNER JOIN dbo.ProjectThingLinks PTL1 ON
     PTL1.ProjectID = P.ProjectID
LEFT OUTER JOIN dbo.ProjectThingLinks PTL2 ON
     PTL2.ProjectID = ThingID = PTL1.ThingID AND
     PTL2.CreatedDate > PTL1.CreatedDate
INNER JOIN dbo.Things T ON
     T.ThingID = PTL1.ThingID
     PTL2.ThingID IS NULL

Once you decide on your business rules for handling two rows that have identical CreatedDate values you may need to tweak the query.

Also, as a side note, a table called "Things" is typically a good sign of a problem with your database design. Tables should represent distinct real life entities. That kind of generality will usually result in problems in the future. If these are resources then they will probably share certain attributes beyond just a name. Maybe your case is a very special case, but most likely not. ;)

share|improve this answer
I had stealthed my table names a bit :) –  Loofer Dec 17 '08 at 16:22
add comment

Try this:

select p.ProjectID, p.Name, t.ThingID, t.ThingName, l.CreatedDate 
from Project p
inner join (
    select ProjectID, max(CreatedDate) as CreatedDate
    from ProjectThingLink
    group by ProjectID
) lm on p.ProjectID = l.ProjectID
inner join ProjectThingLink l on lm.ProjectID = l.ProjectID and lm.CreatedDate = l.CreatedDate
inner joing Thing t on l.ThingID = t.ThingID
share|improve this answer
add comment

You can simply do

SELECT Project.projectId, Project.ProjectName, ThingName
FROM Project  INNER JOIN ProjectThingLink
 ON Project.ProjectID = ProjectThingLink.ProjectID INNER JOIN
                     Thing ON ProjectThingLink.ThingID = Thing.ThingID

Will will get a list of every project with every thing linked to it like this:

1 | Project1 | SomeThing

1 | Project1 | ThisThing

2 | Project2 | ThatThing

share|improve this answer
add comment

select * from project inner join (select projectid,max(createdate) as maxdate from projectThingLink group by projectid) as a on projectid = a.projectid and createDate = a.maxDate

share|improve this answer
add comment

Your Answer


