How to work with a subquery returning more than 1 value

Question

In my case, I have 2 Project and Activity tables, and they look like this.

A project is a table at the top and activity at the bottom.

In the Activity table, activityID and projectID are Primary Keys.

What I'm trying to achieve is to create a view that returns all projects that have actions that have endDate later than ProjectededEndDate.

In conclusion, I want to do this:

SELECT *
FROM Project
WHERE (SELECT MAX(endDate) FROM Activity GROUP BY projectID) > projectedEndDate

But I get the following error:

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

thanks

Answer 1

The problem is that it GROUP BYreturns one row for the project identifier.

You need to restructure the request to use the connection:

SELECT p.*
FROM Project p
JOIN (
    SELECT projectID, MAX(endDate) as maxEnd
    FROM Activity
    GROUP BY projectID
) a ON a.projectID = p.projectID
WHERE a.maxEnd > projectedEndDate

, .

Answer 2

, , . :

SELECT p.*
FROM Project p
WHERE (SELECT MAX(a.endDate)
       FROM Activity a
       WHERE a.projectId = p.projectId
      ) > p.projectedEndDate

, GROUP BY .

Answer 3

Here's a clean version of JOIN:

SELECT DISTINCT p.*
FROM Project p
JOIN Activity a on a.projectID = p.projectID
AND a.endDate > p.projectedEndDate

IMHO it's pretty neat and tidy.