CODEWITHSUNDEEP
sqlsql-server
Sebastian Garcia
Sebastian Garcia

Reputation: 65

How to deal with subquery returning more than 1 value

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

Tables

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

Upvotes: 6

Views: 139

Answers (3)

Bohemian
Bohemian

Reputation: 425043

Here's a pure JOIN version:

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

IMHO it's quite neat and tidy.

Upvotes: 2

Sergey Kalinichenko
Sergey Kalinichenko

Reputation: 726599

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.

Upvotes: 4

Gordon Linoff
Gordon Linoff

Reputation: 1269873

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.

Upvotes: 5

Related Questions