How to deal with subquery returning more than 1 value

Question

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

Project is the table at the top and Activity at the bottom.

In the Activity table activityID and projectID are Primary Keys.

What I am trying to achieve is to create a View that returns All projects that have Activities that have endDate later than the Project projectedEndDate.

In summary 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.

Thank you

Answer 1

You can do what you want with a slight tweak to your query. You just need a correlated subquery:

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

In other words, instead of GROUP BY, you need a correlation clause.

Answer 2

The problem is that GROUP BY returns one row per project ID.

You need to restructure your query to use join:

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

This will produce all projects that have an activity ending past the end date of the project.