Correlated Subqueries?

I need to loop through several rows and find matches for values and the
aggregate a different column. Being a VB type and not as much the sql type
my approach would be to loop through the rows and be done with it. But that
does not fit here I need to do it in stored procedure. I was told that
correlated sub queries are the ticket to do what I wish but I can't seem to
get to to work.
My sql looks like this:
use Prototype_BIDW_TALX
--Variable Declaration
DECLARE @.ParentID int
DECLARE @.UltPID int
DECLARE @.UltParentName varchar (50)
DECLARE @.TotalEEs int
DECLARE @.UltTopAcct bit
set @.ParentID = (select distinct [Parent ID] from Customers)
SELECT DISTINCT [Company ID] as 'Company ID', Name as 'Company Name',
sum(Emps) as 'Employee Count', AcctType as 'Trophy Account' FROM Customers
WHERE [Company ID] in (Select distinct [CompanyParent ID] from Customers)
group by [Company ID] , [Name],[accttype]
I get this error:
Sub query returned more than 1 value. This is not permitted when the sub
query follows =, !=, <, <= , >, >= or when the sub query is used as an
expression.
Isn't the correlated sub query supposed to return more than one value so you
can basically loop through?
What am I missing?
LPA few comments:
A) You don't need 'DISTINCT' - you're already grouping.
B) I'm not sure exactly why you're getting that error, but I suspect it's
because you're not qualifying the columns fully (tablename.columnname).
C) You may have more luck (and better performance) using EXISTS instead of
IN:
SELECT
[Company ID] as 'Company ID',
Name as 'Company Name',
SUM(Emps) as 'Employee Count',
AcctType as 'Trophy Account'
FROM Customers
WHERE
EXISTS
(SELECT *
FROM Customers C1
WHERE C1.[CompanyParent ID] = Customers.[Company ID])
GROUP BY
[Company ID] ,
[Name],
[accttype]
Also, I would recommend that in the future you refrain from using spaces in
your column names. IMO it really achieves nothing other than making your
code both harder to read and write.
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"LP" <LP@.discussions.microsoft.com> wrote in message
news:73EC5EAB-0C5E-49E0-B399-81969D89C221@.microsoft.com...
> I need to loop through several rows and find matches for values and the
> aggregate a different column. Being a VB type and not as much the sql
type
> my approach would be to loop through the rows and be done with it. But
that
> does not fit here I need to do it in stored procedure. I was told that
> correlated sub queries are the ticket to do what I wish but I can't seem
to
> get to to work.
> My sql looks like this:
> use Prototype_BIDW_TALX
> --Variable Declaration
> DECLARE @.ParentID int
> DECLARE @.UltPID int
> DECLARE @.UltParentName varchar (50)
> DECLARE @.TotalEEs int
> DECLARE @.UltTopAcct bit
> set @.ParentID = (select distinct [Parent ID] from Customers)
> SELECT DISTINCT [Company ID] as 'Company ID', Name as 'Company Name',
> sum(Emps) as 'Employee Count', AcctType as 'Trophy Account' FROM
Customers
> WHERE [Company ID] in (Select distinct [CompanyParent ID] from Customers)
> group by [Company ID] , [Name],[accttype]
> I get this error:
> Sub query returned more than 1 value. This is not permitted when the sub
> query follows =, !=, <, <= , >, >= or when the sub query is used as an
> expression.
> Isn't the correlated sub query supposed to return more than one value so
you
> can basically loop through?
> What am I missing?
> LP
>|||LP,
I don't see a correlated subquery here. You have two subqueries,
(select distinct [Parent ID] from Customers) and (select distinct
[CompanyParent ID] from Customers), both of which look like standalone
queries, and one or the other returns more than one row, apparently. A
subquery is correlated if it depends on some column value from the outer
query, as in
select ...
from someTable as T1
where [condition contains a query like:] (select ... from anywhere where
[condition or expression in select list includes T1.somecolumn])
It's very hard to guess what you want here. If you could give the table
declarations, sample data, and show the output you are looking for, it
would help.
Steve Kass
Drew University
LP wrote:
>I need to loop through several rows and find matches for values and the
>aggregate a different column. Being a VB type and not as much the sql type
>my approach would be to loop through the rows and be done with it. But that
>does not fit here I need to do it in stored procedure. I was told that
>correlated sub queries are the ticket to do what I wish but I can't seem to
>get to to work.
>My sql looks like this:
>use Prototype_BIDW_TALX
>--Variable Declaration
>DECLARE @.ParentID int
>DECLARE @.UltPID int
>DECLARE @.UltParentName varchar (50)
>DECLARE @.TotalEEs int
>DECLARE @.UltTopAcct bit
>set @.ParentID = (select distinct [Parent ID] from Customers)
>SELECT DISTINCT [Company ID] as 'Company ID', Name as 'Company Name',
>sum(Emps) as 'Employee Count', AcctType as 'Trophy Account' FROM Customers
>WHERE [Company ID] in (Select distinct [CompanyParent ID] from Customers)
>group by [Company ID] , [Name],[accttype]
>I get this error:
>Sub query returned more than 1 value. This is not permitted when the sub
>query follows =, !=, <, <= , >, >= or when the sub query is used as an
>expression.
>Isn't the correlated sub query supposed to return more than one value so you
>can basically loop through?
>What am I missing?
>LP
>
>|||On Mon, 6 Dec 2004 11:41:02 -0800, "LP" <LP@.discussions.microsoft.com>
wrote:
>set @.ParentID = (select distinct [Parent ID] from Customers)
...
>I get this error:
>Sub query returned more than 1 value. This is not permitted when the sub
>query follows =, !=, <, <= , >, >= or when the sub query is used as an
>expression.
>Isn't the correlated sub query supposed to return more than one value so you
>can basically loop through?
Whatever it is you have in mind, in that statement you are assigning
to a simple scalar variable, it can only hold one value.
J.|||I have the following data in a SQL 2000 table and I'm trying to attain some
information from it using a subquery.
I want to find for every row the ConsortiumID and the total employees.
The first thing I'm doing is to find the Parent ID where they are null and
then update the ConsortiumID with the CustID
since the absence of a ParentID means that the customer is indeed the top of
the consortium.
I then update the columns ParentID and ConsortiumID with tha value of
CustID. All that is done.
Now for the sub query, I'm here attempting to match the CustomerID with its
Consortium kind of a top level hierarchy.
I've tried several things and none have worked.
CustID ParentID ConsortiumID Name ConsortiumName EEs TotalEEs
1 4 A 20
2 4 B 40
3 4 C
4 9 ABC 200
5 8 X
6 8 Y
7 8 Z
8 9 XYZ 210
9 9 ABC-XYZ
10 5 X1
11 5 X2
12 12 PQ 90
13 12 P 50
14 12 Q 40
15 15 MD
16 15 M 240
17 15 D 110
18 17 D1 60
19 17 D2 70
20 16 M1 50
Ideally I want something like this.
CustID ParentID ConsortiumID Name ConsortiumName EEs TotalEEs
1 4 A 20 470
2 4 B 40 470
3 4 C 470
4 9 ABC 200 470
5 8 X 470
6 8 Y 470
7 8 Z 470
8 9 XYZ 210 470
9 9 ABC-XYZ 470
10 5 X1 470
11 5 X2 470
12 12 PQ 90 180
13 12 P 50 180
14 12 Q 40 180
15 15 MD 530
16 15 M 240 530
17 15 D 110 530
18 17 D1 60 530
19 17 D2 70 530
20 16 M1 50 530
"Steve Kass" wrote:
> LP,
> I don't see a correlated subquery here. You have two subqueries,
> (select distinct [Parent ID] from Customers) and (select distinct
> [CompanyParent ID] from Customers), both of which look like standalone
> queries, and one or the other returns more than one row, apparently. A
> subquery is correlated if it depends on some column value from the outer
> query, as in
> select ...
> from someTable as T1
> where [condition contains a query like:] (select ... from anywhere where
> [condition or expression in select list includes T1.somecolumn])
> It's very hard to guess what you want here. If you could give the table
> declarations, sample data, and show the output you are looking for, it
> would help.
> Steve Kass
> Drew University
> LP wrote:
> >I need to loop through several rows and find matches for values and the
> >aggregate a different column. Being a VB type and not as much the sql type
> >my approach would be to loop through the rows and be done with it. But that
> >does not fit here I need to do it in stored procedure. I was told that
> >correlated sub queries are the ticket to do what I wish but I can't seem to
> >get to to work.
> >
> >My sql looks like this:
> >use Prototype_BIDW_TALX
> >--Variable Declaration
> >DECLARE @.ParentID int
> >DECLARE @.UltPID int
> >DECLARE @.UltParentName varchar (50)
> >DECLARE @.TotalEEs int
> >DECLARE @.UltTopAcct bit
> >
> >set @.ParentID = (select distinct [Parent ID] from Customers)
> >
> >SELECT DISTINCT [Company ID] as 'Company ID', Name as 'Company Name',
> >sum(Emps) as 'Employee Count', AcctType as 'Trophy Account' FROM Customers
> >WHERE [Company ID] in (Select distinct [CompanyParent ID] from Customers)
> >group by [Company ID] , [Name],[accttype]
> >
> >I get this error:
> >Sub query returned more than 1 value. This is not permitted when the sub
> >query follows =, !=, <, <= , >, >= or when the sub query is used as an
> >expression.
> >
> >Isn't the correlated sub query supposed to return more than one value so you
> >can basically loop through?
> >
> >What am I missing?
> >
> >LP
> >
> >
> >
> >
>|||You're really trying to do two very different things at once. The
TotalEEs column is a summary column that is not an attribute of a
CustID, so it's best to compute that separately and select it when you
need the report - I certainly wouldn't put it into the table that has
the CustID values. Separately, you're trying to follow each CustID up
the hierarchy, which needs something more than a correlated subquery -
it needs iteration or recursion, since it can require more than one step.
Here is code that should give you all the pieces you need:
CREATE TABLE LP (
CustID int,
ParentID int,
Name varchar(20),
EEs int
)
insert into LP
select
CustID,
ParentID,
Name,
EEs
from somewhere -- from the table pictured in your post
go
create function Consortium()
returns @.t table (
CustID int primary key,
ConsortiumID int,
EEs int,
Depth int
) as begin
declare @.d int
set @.d = 0
insert into @.t
select CustID, CustID, EEs, @.d
from LP
where ParentID is null
while @.@.rowcount > 0 begin
set @.d = @.d + 1
insert into @.t
select LP.CustID, T.ConsortiumID, LP.EEs, @.d
from LP join @.t T
on LP.ParentID = T.CustID
and T.Depth = @.d - 1
end
return
end
go
select * from LP
select * from Consortium()
select
LP.CustID,
C.ConsortiumID,
(select sum(EEs) from Consortium() as CS
where CS.ConsortiumID = C.ConsortiumID) as TotalEEs
from
LP,
Consortium() as C
where LP.CustID = C.CustID
go
drop table LP
drop function Consortium
SK
LP wrote:
>I have the following data in a SQL 2000 table and I'm trying to attain some
>information from it using a subquery.
>I want to find for every row the ConsortiumID and the total employees.
>The first thing I'm doing is to find the Parent ID where they are null and
>then update the ConsortiumID with the CustID
>since the absence of a ParentID means that the customer is indeed the top of
>the consortium.
>I then update the columns ParentID and ConsortiumID with tha value of
>CustID. All that is done.
>Now for the sub query, I'm here attempting to match the CustomerID with its
>Consortium kind of a top level hierarchy.
>I've tried several things and none have worked.
>CustID ParentID ConsortiumID Name ConsortiumName EEs TotalEEs
>1 4 A 20
>2 4 B 40
>3 4 C
>4 9 ABC 200
>5 8 X
>6 8 Y
>7 8 Z
>8 9 XYZ 210
>9 9 ABC-XYZ
>10 5 X1
>11 5 X2
>12 12 PQ 90
>13 12 P 50
>14 12 Q 40
>15 15 MD
>16 15 M 240
>17 15 D 110
>18 17 D1 60
>19 17 D2 70
>20 16 M1 50
>
>Ideally I want something like this.
>CustID ParentID ConsortiumID Name ConsortiumName EEs TotalEEs
>1 4 A 20 470
>2 4 B 40 470
>3 4 C 470
>4 9 ABC 200 470
>5 8 X 470
>6 8 Y 470
>7 8 Z 470
>8 9 XYZ 210 470
>9 9 ABC-XYZ 470
>10 5 X1 470
>11 5 X2 470
>12 12 PQ 90 180
>13 12 P 50 180
>14 12 Q 40 180
>15 15 MD 530
>16 15 M 240 530
>17 15 D 110 530
>18 17 D1 60 530
>19 17 D2 70 530
>20 16 M1 50 530
>
>"Steve Kass" wrote:
>
>>LP,
>> I don't see a correlated subquery here. You have two subqueries,
>>(select distinct [Parent ID] from Customers) and (select distinct
>>[CompanyParent ID] from Customers), both of which look like standalone
>>queries, and one or the other returns more than one row, apparently. A
>>subquery is correlated if it depends on some column value from the outer
>>query, as in
>>select ...
>>from someTable as T1
>>where [condition contains a query like:] (select ... from anywhere where
>>[condition or expression in select list includes T1.somecolumn])
>>It's very hard to guess what you want here. If you could give the table
>>declarations, sample data, and show the output you are looking for, it
>>would help.
>>Steve Kass
>>Drew University
>>LP wrote:
>>
>>I need to loop through several rows and find matches for values and the
>>aggregate a different column. Being a VB type and not as much the sql type
>>my approach would be to loop through the rows and be done with it. But that
>>does not fit here I need to do it in stored procedure. I was told that
>>correlated sub queries are the ticket to do what I wish but I can't seem to
>>get to to work.
>>My sql looks like this:
>>use Prototype_BIDW_TALX
>>--Variable Declaration
>>DECLARE @.ParentID int
>>DECLARE @.UltPID int
>>DECLARE @.UltParentName varchar (50)
>>DECLARE @.TotalEEs int
>>DECLARE @.UltTopAcct bit
>>set @.ParentID = (select distinct [Parent ID] from Customers)
>>SELECT DISTINCT [Company ID] as 'Company ID', Name as 'Company Name',
>>sum(Emps) as 'Employee Count', AcctType as 'Trophy Account' FROM Customers
>>WHERE [Company ID] in (Select distinct [CompanyParent ID] from Customers)
>>group by [Company ID] , [Name],[accttype]
>>I get this error:
>>Sub query returned more than 1 value. This is not permitted when the sub
>>query follows =, !=, <, <= , >, >= or when the sub query is used as an
>>expression.
>>Isn't the correlated sub query supposed to return more than one value so you
>>can basically loop through?
>>What am I missing?
>>LP
>>
>>
>>