msk msk - 2 months ago 6
SQL Question

Need assistance with a SQL query

I need help with a query that I can't seem to figure out how to compose. (SQL Server)

I have two tables, "Project" and "ProjectStatus" that have these fields: (I have omitted irrelevant columns)

Project

PROJID (PK) NAME


ProjectStatus

STATUSID PROJID (FK, references projid in project table) CHANGEDDATE





I want to get a list of all projects that have a statusid of either 3 or 5, so my obvious first attempt was to do the following:

SELECT p.PROJID, p.NAME
FROM Project AS p
INNER JOIN ProjectStatus AS s
ON s.PROJID=p.PROJID
WHERE s.STATUSID IN (3, 5)


(ignore any potential syntax errors in this query, I just wrote it down from memory - it worked when I tested)

Now, it turns out that every time someone changes the project status on a project, a new entry goes in to the ProjectStatus table. It doesn't just update the STATUSID value as I initially thought.

That means that I have to


  1. Get the most recent status from the ProjectStatus table

  2. Execute the same query as above, except the STATUSID must be the latest and not just any random STATUSID connected to the project



The SQL to get the current status of a project is:

SELECT TOP 1 STATUSID
FROM ProjectStatus
WHERE PROJID=(any given project id)
ORDER BY CHANGEDDATE DESC


MILLION DOLLAR QUESTION

How would I incorporate this query into the first one to get the desired result?
(or any other query that would get me what I want)

Answer

It looks like you need cross apply here. Notice it is available in SQL Server starting from 2005 version.

SELECT p.PROJID, p.NAME, S.STATUSID 
FROM Project AS p 
    CROSS APPLY (
         SELECT TOP 1 STATUSID 
         FROM ProjectStatus as S
         WHERE S.PROJID=p.PROJID
         ORDER BY CHANGEDDATE DESC
    ) as S 
WHERE s.STATUSID IN (3, 5)
Comments