"Subquery returned more than one result" when it's

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

"Subquery returned more than one result" when it's

Author

Topic

b3bel
Starting Member

6 Posts

Posted - 2010-09-14 : 04:51:52

Hi,

I am have the following query:

SELECT COUNT(ws2.WorkerID) AS workercount, s1.SkillName, 
    (select COUNT(s2.SkillID) as workercount--,s2.SkillName
     from WorkerSkills as ws1 
       inner join Workers on ws1.WorkerID = Workers.WorkerID 
       INNER Join Skills as s2 on ws1.SkillID=s2.SkillID
      where TeamID=11
      group by s2.SkillID ) as teamcount
FROM  Skills as s1
INNER JOIN WorkerSkills as ws2 ON s1.SkillID = ws2.SkillID 
INNER JOIN Workers ON ws2.WorkerID = Workers.WorkerID
WHERE Workers.TeamID=11
group by s1.SkillName

And I am getting 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.

which I understand why.
The problem is that, in my case, I want the teamcount column to have a specific value for each row. And since I am grouping both queries by skillName or skillID, that should work.

How do I write this query so that I won't get this error but get the results that I want ?

A perfection of means, and confusion of aims, seems to be our main problem.
Albert Einstein

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-09-14 : 05:47:02

What happens when you try this?


SELECT COUNT(ws2.WorkerID) AS workercount, s1.SkillName, 
    (select COUNT(s2.SkillID) as workercount--,s2.SkillName
     from WorkerSkills as ws1 
       inner join Workers on ws1.WorkerID = Workers.WorkerID 
       INNER Join Skills as s2 on ws1.SkillID=s2.SkillID
      where TeamID=11
      ) as teamcount
FROM  Skills as s1
INNER JOIN WorkerSkills as ws2 ON s1.SkillID = ws2.SkillID 
INNER JOIN Workers ON ws2.WorkerID = Workers.WorkerID
WHERE Workers.TeamID=11
group by s1.SkillName

Madhivanan

Failing to plan is Planning to fail

b3bel
Starting Member

6 Posts

Posted - 2010-09-14 : 07:39:45

quote:

Originally posted by madhivanan

What happens when you try this?


SELECT COUNT(ws2.WorkerID) AS workercount, s1.SkillName, 
    (select COUNT(s2.SkillID) as workercount--,s2.SkillName
     from WorkerSkills as ws1 
       inner join Workers on ws1.WorkerID = Workers.WorkerID 
       INNER Join Skills as s2 on ws1.SkillID=s2.SkillID
      where TeamID=11
      ) as teamcount
FROM  Skills as s1
INNER JOIN WorkerSkills as ws2 ON s1.SkillID = ws2.SkillID 
INNER JOIN Workers ON ws2.WorkerID = Workers.WorkerID
WHERE Workers.TeamID=11
group by s1.SkillName

Madhivanan

Failing to plan is Planning to fail

Thnks for your reply

I get the right results for the workerCount column and a repeated, and therefore wrong, result in the teamcount

A perfection of means, and confusion of aims, seems to be our main problem.
Albert Einstein

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources