subquery - SQL Server Forums

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 2000 Forums

Transact-SQL (2000)

subquery

Author

Topic

Hadware
Starting Member

6 Posts

Posted - 2006-01-06 : 09:15:19

I get this error when attampting to perform the following select statement:
select count(a_id) As [number] from tbl_2 where p_id=(select p_id from tbl_2 where active = '1')
It doesn't look like it will work jsut by looking at it, but how would you get this to work when the p_id from tbl_2 has more than 1 value?
Also, I would need a count on each of the values.
The error is as follows:
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

Thats it. Thanks for any help.
Eric

sachinsamuel
Constraint Violating Yak Guru

383 Posts

Posted - 2006-01-06 : 09:18:55

select count(a_id) As [number] from tbl_2 where p_id
in
(
select p_id from tbl_2 where active = '1'
)

For Count on use group by clause. Check Bol for more details.

Don't sit back because of failure. It will come back to check if you still available. -- Binu

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2006-01-06 : 09:20:06

where p_id in (select p_id from tbl_2 where active = '1')

Duane.

"It's a thankless job, but I've got a lot of Karma to burn off."

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2006-01-06 : 09:20:19

Use an aggregate in your subquery, such as "select max(p_id)" or "select min(p_id)" to ensure that only one value is returned. But you should also check your desgin to see why you are trying to match against more than one value...

X002548
Not Just a Number

15586 Posts

Posted - 2006-01-06 : 09:22:35

Can be written as


SELECT COUNT(a_id) AS [number] 
  FROM tbl_2 
 WHERE p_id IN (SELECT p_id 
		  FROM tbl_2 
		 WHERE active = '1')

But all you really need is



SELECT COUNT(a_id) AS [number] 
  FROM tbl_2 
 WHERE active = '1'

Look at the hint link in my sig

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2006-01-06 : 09:23:11

Actually, I think this is what you want:

select	a_id,
	count(*) As [number]
	from tbl_2
where active = '1'
group by a_id

X002548
Not Just a Number

15586 Posts

Posted - 2006-01-06 : 09:25:06

quote:
Originally posted by blindman

Actually, I think this is what you want:
select a_id,
count(*) As [number]
from tbl_2
where active = '1'
group by a_id

Words can not express......

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

Hadware
Starting Member

6 Posts

Posted - 2006-01-06 : 09:26:58

Using the 'in' instead of the '=' does make the entire count. But what I'd like to accomplish is getting all the values from tbl_2 where active = '1' and use each of those values to get a count of the a_id's for each value in tbl_1. I am told not to use a cursor but it seems to be the only way to accomplish this from what I am reading. Thanks.

Hadware
Starting Member

6 Posts

Posted - 2006-01-06 : 09:31:31

man you guys replay fast.
here is what I have and it works:
select count(a_id) As [number] from tbl_1 where p_id in (select p_id from tbl_2 where active = '1') group by p_id

It gives me the count of all the a_id's in each p_id. Now I have to figure out how to distinguish each group and use them for some update statements.

X002548
Not Just a Number

15586 Posts

Posted - 2006-01-06 : 09:40:03

Isn't this what you want?


SELECT p_id, COUNT(a_id) AS [number] 
  FROM tbl_2 
 WHERE active = '1'
GROUP BY p_id

Also, please read the hint link in my sig, and post what it asks for there for a total solution

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-01-06 : 09:46:18

Post some sample data and the result you want

Madhivanan

Failing to plan is Planning to fail

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2006-01-06 : 10:24:09

quote:
Originally posted by X002548

Isn't this what you want?

SELECT p_id, COUNT(a_id) AS [number]
FROM tbl_2
WHERE active = '1'
GROUP BY p_id

Maybe, maybe not. Notice in his last post he mentions a table " tbl_1", which he did not include in his first post...

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2006-01-06 : 10:27:26

Something like this?:

select	tbl_1.p_id,
	count(*) As [number]
from	tbl_1
	inner join tbl_2 on tbl_1.p_id = tbl_2.p_id
where	tbl_2.active = '1'
group by tbl_1.p_id

Hadware
Starting Member

6 Posts

Posted - 2006-01-06 : 11:31:15

OK,
The result I want is
p_id number
1 35
2 12

--Using the p_id values and making certain that the 'number' is not over '50'
declare @numbervalue int
Select @numbervalue = count(number) from number (whatever the number value is from the results above)
If @numbervalue >= '50' begin
--do something different first
end
else
if @numbervalue < '50' begin
-- I want to select another id from each one of the p_id values in tbl_1
declare @a_id int, @a_id2 int
select @a_id = a_id from tbl_1 where p_id = '1'(this is the p_id from the first result) and n_id = '1'(this selected value will always be '1')
select @a_id2 = a_id from tbl_1 where p_id = '2'(this is the p_id from the first result) and n_id = '1'(this selected value will always be '1')

--Now, using that a_id (only one value), I will select the top 3 and use those a_id's to perform the updates needed.
Select top 3 (a_id) from tbl_1 where a_id >= @a_id
Select top 3 (a_id) from tbl_1 where a_id >= @a_id2

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources