Need assistance with a SQL query

Question

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 1

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)

Answer 2

Try this

SELECT p.PROJID, p.NAME
FROM Project AS p 
INNER JOIN ProjectStatus AS s 
ON s.PROJID=p.PROJID 
WHERE s.STATUSID >= 3 && s.STATUSID <= 5