Wonder if anyone can help
If I wanted 2 Values from a Correlated Subquery (same table join etc) would i have to have 2 subqueries or is there a better way to do it ?
ie.
SELECT t1.MyMainCode,
(SELECT SUM(t2.Qty) FROM t2 WHERE t2.MyMainCode = t1.MyMainCode) tQty,
(SELECT SUM(t2.Qty2) FROM t2 WHERE t2.MyMainCode = t1.MyMainCode) tQty2
FROM t1
Any Help appreciated
GWselect t1.MyMainCode
, sum(t2.Qty)
, sum(t2.Qty2)
from t1
left outer
join t2
on t1.MyMainCode = t2.MyMainCode
group
by t1.MyMainCode
rudy
http://r937.com/
Showing posts with label correlated. Show all posts
Showing posts with label correlated. Show all posts
Thursday, March 29, 2012
Correlated Subquery
Hi,
Correlated subquery is written in the following syntax in Oracle.
Select Ename,Sal from Employee X where 5= (Select count(distinct(sal)) from
Emplyee where sal>=X.sal)
How can this be written in SQL Server?
Please throw some light.
Thanks,
Su ManIn SQL server you use HAVING clause. For more information and the syntx
please refer to Books Online available.
thanks and regards
Chandra
"Su Man" wrote:
> Hi,
> Correlated subquery is written in the following syntax in Oracle.
> Select Ename,Sal from Employee X where 5= (Select count(distinct(sal)) fro
m
> Emplyee where sal>=X.sal)
> How can this be written in SQL Server?
> Please throw some light.
> Thanks,
> Su Man
>
>|||Your query is valid in SQL server also
Roji. P. Thomas
Net Asset Management
https://www.netassetmanagement.com
"Su Man" <subu501@.yahoo.com> wrote in message
news:d4sl39$eqi$1@.news.mch.sbs.de...
> Hi,
> Correlated subquery is written in the following syntax in Oracle.
> Select Ename,Sal from Employee X where 5= (Select count(distinct(sal))
> from
> Emplyee where sal>=X.sal)
> How can this be written in SQL Server?
> Please throw some light.
> Thanks,
> Su Man
>|||The query you posted is valid in SQL Server. This is Standard SQL syntax.
Did you try it?
David Portas
SQL Server MVP
--|||It is working. Thanks.
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:gKOdneGg3IlLQOzfRVn-tA@.giganews.com...
> The query you posted is valid in SQL Server. This is Standard SQL syntax.
> Did you try it?
> --
> David Portas
> SQL Server MVP
> --
>
Correlated subquery is written in the following syntax in Oracle.
Select Ename,Sal from Employee X where 5= (Select count(distinct(sal)) from
Emplyee where sal>=X.sal)
How can this be written in SQL Server?
Please throw some light.
Thanks,
Su ManIn SQL server you use HAVING clause. For more information and the syntx
please refer to Books Online available.
thanks and regards
Chandra
"Su Man" wrote:
> Hi,
> Correlated subquery is written in the following syntax in Oracle.
> Select Ename,Sal from Employee X where 5= (Select count(distinct(sal)) fro
m
> Emplyee where sal>=X.sal)
> How can this be written in SQL Server?
> Please throw some light.
> Thanks,
> Su Man
>
>|||Your query is valid in SQL server also
Roji. P. Thomas
Net Asset Management
https://www.netassetmanagement.com
"Su Man" <subu501@.yahoo.com> wrote in message
news:d4sl39$eqi$1@.news.mch.sbs.de...
> Hi,
> Correlated subquery is written in the following syntax in Oracle.
> Select Ename,Sal from Employee X where 5= (Select count(distinct(sal))
> from
> Emplyee where sal>=X.sal)
> How can this be written in SQL Server?
> Please throw some light.
> Thanks,
> Su Man
>|||The query you posted is valid in SQL Server. This is Standard SQL syntax.
Did you try it?
David Portas
SQL Server MVP
--|||It is working. Thanks.
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:gKOdneGg3IlLQOzfRVn-tA@.giganews.com...
> The query you posted is valid in SQL Server. This is Standard SQL syntax.
> Did you try it?
> --
> David Portas
> SQL Server MVP
> --
>
Correlated Subquery
Can you use a correlated subquery in a join expression, when you reference
the table your joining to?
Hi Ben
An example of what you are trying to do would be useful! See
http://www.aspfaq.com/etiquette.asp?id=5006
I assume that you are soing something like:
SELECT a.col1, a.col2, d.col1
FROM tablea a
JOIN ( SELECT b.col1, c.col2
FROM tableb b
JOIN tablec c ON c.cold1 = b.col1 ) d where a.col1 = d.col1
and want to do
SELECT a.col1, a.col2, d.col1
FROM tablea a
JOIN ( SELECT b.col1, c.col2
FROM tableb b
JOIN tablec c ON c.cold1 = b.col1
WHERE a.col1 = c.col1 ) d
Then the answer is no.
In SQL 2005 there is the CROSS APPLY operator where you could write your
derived table as a function
CREATE FUNCTION dbo.fn_derivedtbl(@.col1 AS INT) RETURNS TABLE
AS
RETURNS ( SELECT b.col1, c.col2
FROM tableb b
JOIN tablec c ON c.cold1 = b.col1
WHERE c.col1 = @.col1 )
SELECT a.col1, a.col2, d.col1
FROM tablea a
CROSS APPLY dbo.fn_derivedtbl(a.col1) AS d
N.B None of the scripts are tested!
John
"Ben UK" wrote:
> Can you use a correlated subquery in a join expression, when you reference
> the table your joining to?
|||To my knowledge, no, you cannot. Correlated subqueries can be used only in
the Select and the Where statements.
However, if you make a new post explaining exactly what you are trying to
achieve, maybe someone will be able to provide you with an alternate
solution.
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)
"Ben UK" <BenUK@.discussions.microsoft.com> wrote in message
news:E2A87171-A1E4-4209-A056-E577B7A8326C@.microsoft.com...
> Can you use a correlated subquery in a join expression, when you reference
> the table your joining to?
the table your joining to?
Hi Ben
An example of what you are trying to do would be useful! See
http://www.aspfaq.com/etiquette.asp?id=5006
I assume that you are soing something like:
SELECT a.col1, a.col2, d.col1
FROM tablea a
JOIN ( SELECT b.col1, c.col2
FROM tableb b
JOIN tablec c ON c.cold1 = b.col1 ) d where a.col1 = d.col1
and want to do
SELECT a.col1, a.col2, d.col1
FROM tablea a
JOIN ( SELECT b.col1, c.col2
FROM tableb b
JOIN tablec c ON c.cold1 = b.col1
WHERE a.col1 = c.col1 ) d
Then the answer is no.
In SQL 2005 there is the CROSS APPLY operator where you could write your
derived table as a function
CREATE FUNCTION dbo.fn_derivedtbl(@.col1 AS INT) RETURNS TABLE
AS
RETURNS ( SELECT b.col1, c.col2
FROM tableb b
JOIN tablec c ON c.cold1 = b.col1
WHERE c.col1 = @.col1 )
SELECT a.col1, a.col2, d.col1
FROM tablea a
CROSS APPLY dbo.fn_derivedtbl(a.col1) AS d
N.B None of the scripts are tested!
John
"Ben UK" wrote:
> Can you use a correlated subquery in a join expression, when you reference
> the table your joining to?
|||To my knowledge, no, you cannot. Correlated subqueries can be used only in
the Select and the Where statements.
However, if you make a new post explaining exactly what you are trying to
achieve, maybe someone will be able to provide you with an alternate
solution.
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)
"Ben UK" <BenUK@.discussions.microsoft.com> wrote in message
news:E2A87171-A1E4-4209-A056-E577B7A8326C@.microsoft.com...
> Can you use a correlated subquery in a join expression, when you reference
> the table your joining to?
Correlated Subquery
Can you use a correlated subquery in a join expression, when you reference
the table your joining to?Hi Ben
An example of what you are trying to do would be useful! See
http://www.aspfaq.com/etiquette.asp?id=5006
I assume that you are soing something like:
SELECT a.col1, a.col2, d.col1
FROM tablea a
JOIN ( SELECT b.col1, c.col2
FROM tableb b
JOIN tablec c ON c.cold1 = b.col1 ) d where a.col1 = d.col1
and want to do
SELECT a.col1, a.col2, d.col1
FROM tablea a
JOIN ( SELECT b.col1, c.col2
FROM tableb b
JOIN tablec c ON c.cold1 = b.col1
WHERE a.col1 = c.col1 ) d
Then the answer is no.
In SQL 2005 there is the CROSS APPLY operator where you could write your
derived table as a function
CREATE FUNCTION dbo.fn_derivedtbl(@.col1 AS INT) RETURNS TABLE
AS
RETURNS ( SELECT b.col1, c.col2
FROM tableb b
JOIN tablec c ON c.cold1 = b.col1
WHERE c.col1 = @.col1 )
SELECT a.col1, a.col2, d.col1
FROM tablea a
CROSS APPLY dbo.fn_derivedtbl(a.col1) AS d
N.B None of the scripts are tested!
John
"Ben UK" wrote:
> Can you use a correlated subquery in a join expression, when you reference
> the table your joining to?|||To my knowledge, no, you cannot. Correlated subqueries can be used only in
the Select and the Where statements.
However, if you make a new post explaining exactly what you are trying to
achieve, maybe someone will be able to provide you with an alternate
solution.
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)
"Ben UK" <BenUK@.discussions.microsoft.com> wrote in message
news:E2A87171-A1E4-4209-A056-E577B7A8326C@.microsoft.com...
> Can you use a correlated subquery in a join expression, when you reference
> the table your joining to?
the table your joining to?Hi Ben
An example of what you are trying to do would be useful! See
http://www.aspfaq.com/etiquette.asp?id=5006
I assume that you are soing something like:
SELECT a.col1, a.col2, d.col1
FROM tablea a
JOIN ( SELECT b.col1, c.col2
FROM tableb b
JOIN tablec c ON c.cold1 = b.col1 ) d where a.col1 = d.col1
and want to do
SELECT a.col1, a.col2, d.col1
FROM tablea a
JOIN ( SELECT b.col1, c.col2
FROM tableb b
JOIN tablec c ON c.cold1 = b.col1
WHERE a.col1 = c.col1 ) d
Then the answer is no.
In SQL 2005 there is the CROSS APPLY operator where you could write your
derived table as a function
CREATE FUNCTION dbo.fn_derivedtbl(@.col1 AS INT) RETURNS TABLE
AS
RETURNS ( SELECT b.col1, c.col2
FROM tableb b
JOIN tablec c ON c.cold1 = b.col1
WHERE c.col1 = @.col1 )
SELECT a.col1, a.col2, d.col1
FROM tablea a
CROSS APPLY dbo.fn_derivedtbl(a.col1) AS d
N.B None of the scripts are tested!
John
"Ben UK" wrote:
> Can you use a correlated subquery in a join expression, when you reference
> the table your joining to?|||To my knowledge, no, you cannot. Correlated subqueries can be used only in
the Select and the Where statements.
However, if you make a new post explaining exactly what you are trying to
achieve, maybe someone will be able to provide you with an alternate
solution.
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)
"Ben UK" <BenUK@.discussions.microsoft.com> wrote in message
news:E2A87171-A1E4-4209-A056-E577B7A8326C@.microsoft.com...
> Can you use a correlated subquery in a join expression, when you reference
> the table your joining to?
Correlated subqueries? Please help!
Hi there,
I want to do something very simple, but in the most efficient way possible.
Let's say I have these two tables:
tblContacts(contact_id, contact_name)
tblPhones(phone_id, contact_id, phone_number, phone_rank)
When querying my contacts, I need to get the phone numbers in tblPhones
whose phone_rank is either = 1 or 2, and I want to display them in two
columns of the resultset, like this:
Name Phone1 Phone2
John Smith 555-5555 666-6666
I've tried correlated subqueries:
select contact_id, contact_name,
(select phone_number from tblPhones where phone_rank=1 and
tblPhones.contact_id = tblContacts.contact_id) as Phone1,
(select phone_number from tblPhones where phone_rank=2 and
tblPhones.contact_id = tblContacts.contact_id) as Phone2
from tblContacts
It works, but seems a bit slow...
I'm sure there are better ways to do this, but I can't figure it out and am
in a hurry...
Any hints would be appreciated!
TIA
Paul Dussault, MCPBest done client side but here's one method.
Works only if a contact can have only one phone per rank.
Untested:
select C.contact_name as [name],
(select phone_number from tblPhones where phone_rank = 1 and contact_ID =
C.contact_ID) as phone1,
(select phone_number from tblPhones where phone_rank = 2 and contact_ID =
C.contact_ID) as phone2
from tblContacts C
"Paul Dussault" <paulduss@.hotmail.com> wrote in message
news:OCuGNWWYFHA.3572@.TK2MSFTNGP12.phx.gbl...
> Hi there,
> I want to do something very simple, but in the most efficient way
> possible.
> Let's say I have these two tables:
> tblContacts(contact_id, contact_name)
> tblPhones(phone_id, contact_id, phone_number, phone_rank)
> When querying my contacts, I need to get the phone numbers in tblPhones
> whose phone_rank is either = 1 or 2, and I want to display them in two
> columns of the resultset, like this:
>
> Name Phone1 Phone2
> John Smith 555-5555 666-6666
>
> I've tried correlated subqueries:
> select contact_id, contact_name,
> (select phone_number from tblPhones where phone_rank=1 and
> tblPhones.contact_id = tblContacts.contact_id) as Phone1,
> (select phone_number from tblPhones where phone_rank=2 and
> tblPhones.contact_id = tblContacts.contact_id) as Phone2
> from tblContacts
> It works, but seems a bit slow...
> I'm sure there are better ways to do this, but I can't figure it out and
> am in a hurry...
> Any hints would be appreciated!
>
> TIA
>
> Paul Dussault, MCP
>
>|||Paul Dussault wrote:
> Hi there,
> I want to do something very simple, but in the most efficient way possible
.
> Let's say I have these two tables:
> tblContacts(contact_id, contact_name)
> tblPhones(phone_id, contact_id, phone_number, phone_rank)
> When querying my contacts, I need to get the phone numbers in tblPhones
> whose phone_rank is either = 1 or 2, and I want to display them in two
> columns of the resultset, like this:
>
> Name Phone1 Phone2
> John Smith 555-5555 666-6666
>
> I've tried correlated subqueries:
> select contact_id, contact_name,
> (select phone_number from tblPhones where phone_rank=1 and
> tblPhones.contact_id = tblContacts.contact_id) as Phone1,
> (select phone_number from tblPhones where phone_rank=2 and
> tblPhones.contact_id = tblContacts.contact_id) as Phone2
> from tblContacts
> It works, but seems a bit slow...
> I'm sure there are better ways to do this, but I can't figure it out and a
m
> in a hurry...
> Any hints would be appreciated!
Something like this:
select contact_id, contact_name, p1.phone_number, p2.phone_number
from tblContacts c
left outer join tblPhones p1 on
p1.contact_id = c.contact_id and
p1.phone_rank = 1
left outer join tblPhones p2 on
p2.contact_id = c.contact_id and
p2.phone_rank = 2
Regards,
Berend. (-:|||Many Thanks!
I didn't think of the aliases...
Much faster!
Paul Dussault
"Berend de Boer" <berend@.xsol.com> wrote in message
news:%23v%23tzjWYFHA.3584@.TK2MSFTNGP12.phx.gbl...
> Paul Dussault wrote:
>
> Something like this:
> select contact_id, contact_name, p1.phone_number, p2.phone_number
> from tblContacts c
> left outer join tblPhones p1 on
> p1.contact_id = c.contact_id and
> p1.phone_rank = 1
> left outer join tblPhones p2 on
> p2.contact_id = c.contact_id and
> p2.phone_rank = 2
> Regards,
> Berend. (-:|||Sorry if I sound ignorant, but how do aliases speed up query execution? I
always figured that, if anything, they would slightly slow down execution
since each alias has to be translated to the full path at run time (or at
least I've always assumed that that was what happened).
Chris
"Paul Dussault" wrote:
> Many Thanks!
> I didn't think of the aliases...
> Much faster!
> Paul Dussault
> "Berend de Boer" <berend@.xsol.com> wrote in message
> news:%23v%23tzjWYFHA.3584@.TK2MSFTNGP12.phx.gbl...
>
>|||They don't really. What sped up your query was changing the correlated
subqueries into Left Joins.
Thomas
"Chris Lieb" <ChrisLieb@.discussions.microsoft.com> wrote in message
news:D4D67BF3-2DBB-4A7E-BD28-C84282929D1C@.microsoft.com...
<snip>sql
I want to do something very simple, but in the most efficient way possible.
Let's say I have these two tables:
tblContacts(contact_id, contact_name)
tblPhones(phone_id, contact_id, phone_number, phone_rank)
When querying my contacts, I need to get the phone numbers in tblPhones
whose phone_rank is either = 1 or 2, and I want to display them in two
columns of the resultset, like this:
Name Phone1 Phone2
John Smith 555-5555 666-6666
I've tried correlated subqueries:
select contact_id, contact_name,
(select phone_number from tblPhones where phone_rank=1 and
tblPhones.contact_id = tblContacts.contact_id) as Phone1,
(select phone_number from tblPhones where phone_rank=2 and
tblPhones.contact_id = tblContacts.contact_id) as Phone2
from tblContacts
It works, but seems a bit slow...
I'm sure there are better ways to do this, but I can't figure it out and am
in a hurry...
Any hints would be appreciated!
TIA
Paul Dussault, MCPBest done client side but here's one method.
Works only if a contact can have only one phone per rank.
Untested:
select C.contact_name as [name],
(select phone_number from tblPhones where phone_rank = 1 and contact_ID =
C.contact_ID) as phone1,
(select phone_number from tblPhones where phone_rank = 2 and contact_ID =
C.contact_ID) as phone2
from tblContacts C
"Paul Dussault" <paulduss@.hotmail.com> wrote in message
news:OCuGNWWYFHA.3572@.TK2MSFTNGP12.phx.gbl...
> Hi there,
> I want to do something very simple, but in the most efficient way
> possible.
> Let's say I have these two tables:
> tblContacts(contact_id, contact_name)
> tblPhones(phone_id, contact_id, phone_number, phone_rank)
> When querying my contacts, I need to get the phone numbers in tblPhones
> whose phone_rank is either = 1 or 2, and I want to display them in two
> columns of the resultset, like this:
>
> Name Phone1 Phone2
> John Smith 555-5555 666-6666
>
> I've tried correlated subqueries:
> select contact_id, contact_name,
> (select phone_number from tblPhones where phone_rank=1 and
> tblPhones.contact_id = tblContacts.contact_id) as Phone1,
> (select phone_number from tblPhones where phone_rank=2 and
> tblPhones.contact_id = tblContacts.contact_id) as Phone2
> from tblContacts
> It works, but seems a bit slow...
> I'm sure there are better ways to do this, but I can't figure it out and
> am in a hurry...
> Any hints would be appreciated!
>
> TIA
>
> Paul Dussault, MCP
>
>|||Paul Dussault wrote:
> Hi there,
> I want to do something very simple, but in the most efficient way possible
.
> Let's say I have these two tables:
> tblContacts(contact_id, contact_name)
> tblPhones(phone_id, contact_id, phone_number, phone_rank)
> When querying my contacts, I need to get the phone numbers in tblPhones
> whose phone_rank is either = 1 or 2, and I want to display them in two
> columns of the resultset, like this:
>
> Name Phone1 Phone2
> John Smith 555-5555 666-6666
>
> I've tried correlated subqueries:
> select contact_id, contact_name,
> (select phone_number from tblPhones where phone_rank=1 and
> tblPhones.contact_id = tblContacts.contact_id) as Phone1,
> (select phone_number from tblPhones where phone_rank=2 and
> tblPhones.contact_id = tblContacts.contact_id) as Phone2
> from tblContacts
> It works, but seems a bit slow...
> I'm sure there are better ways to do this, but I can't figure it out and a
m
> in a hurry...
> Any hints would be appreciated!
Something like this:
select contact_id, contact_name, p1.phone_number, p2.phone_number
from tblContacts c
left outer join tblPhones p1 on
p1.contact_id = c.contact_id and
p1.phone_rank = 1
left outer join tblPhones p2 on
p2.contact_id = c.contact_id and
p2.phone_rank = 2
Regards,
Berend. (-:|||Many Thanks!
I didn't think of the aliases...
Much faster!
Paul Dussault
"Berend de Boer" <berend@.xsol.com> wrote in message
news:%23v%23tzjWYFHA.3584@.TK2MSFTNGP12.phx.gbl...
> Paul Dussault wrote:
>
> Something like this:
> select contact_id, contact_name, p1.phone_number, p2.phone_number
> from tblContacts c
> left outer join tblPhones p1 on
> p1.contact_id = c.contact_id and
> p1.phone_rank = 1
> left outer join tblPhones p2 on
> p2.contact_id = c.contact_id and
> p2.phone_rank = 2
> Regards,
> Berend. (-:|||Sorry if I sound ignorant, but how do aliases speed up query execution? I
always figured that, if anything, they would slightly slow down execution
since each alias has to be translated to the full path at run time (or at
least I've always assumed that that was what happened).
Chris
"Paul Dussault" wrote:
> Many Thanks!
> I didn't think of the aliases...
> Much faster!
> Paul Dussault
> "Berend de Boer" <berend@.xsol.com> wrote in message
> news:%23v%23tzjWYFHA.3584@.TK2MSFTNGP12.phx.gbl...
>
>|||They don't really. What sped up your query was changing the correlated
subqueries into Left Joins.
Thomas
"Chris Lieb" <ChrisLieb@.discussions.microsoft.com> wrote in message
news:D4D67BF3-2DBB-4A7E-BD28-C84282929D1C@.microsoft.com...
<snip>sql
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
>>
>>
>>
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
>>
>>
>>
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?
LP
A 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.
CustIDParentIDConsortiumIDNameConsortiumNameEEsTotalEEs
14A20
24B40
34C
49ABC200
58X
68Y
78Z
89XYZ210
99ABC-XYZ
105X1
115X2
1212PQ90
1312P50
1412Q40
1515MD
1615M240
1715D110
1817D160
1917D270
2016M150
Ideally I want something like this.
CustIDParentIDConsortiumIDNameConsortiumNameEEsTotalEEs
14A20470
24B40470
34C470
49ABC200470
58X470
68Y470
78Z470
89XYZ210470
99ABC-XYZ470
105X1470
115X2470
1212PQ90180
1312P50180
1412Q40180
1515MD530
1615M240530
1715D110530
1817D160530
1917D270530
2016M150530
"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:
>
|||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:
[vbcol=seagreen]
>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.
>CustIDParentIDConsortiumIDNameConsortiumNameEEsTotalEEs
>14A20
>24B40
>34C
>49ABC200
>58X
>68Y
>78Z
>89XYZ210
>99ABC-XYZ
>105X1
>115X2
>1212PQ90
>1312P50
>1412Q40
>1515MD
>1615M240
>1715D110
>1817D160
>1917D270
>2016M150
>
>Ideally I want something like this.
>CustIDParentIDConsortiumIDNameConsortiumNameEEsTotalEEs
>14A20470
>24B40470
>34C470
>49ABC200470
>58X470
>68Y470
>78Z470
>89XYZ210470
>99ABC-XYZ470
>105X1470
>115X2470
>1212PQ90180
>1312P50180
>1412Q40180
>1515MD530
>1615M240530
>1715D110530
>1817D160530
>1917D270530
>2016M150530
>
>"Steve Kass" wrote:
>
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
A 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.
CustIDParentIDConsortiumIDNameConsortiumNameEEsTotalEEs
14A20
24B40
34C
49ABC200
58X
68Y
78Z
89XYZ210
99ABC-XYZ
105X1
115X2
1212PQ90
1312P50
1412Q40
1515MD
1615M240
1715D110
1817D160
1917D270
2016M150
Ideally I want something like this.
CustIDParentIDConsortiumIDNameConsortiumNameEEsTotalEEs
14A20470
24B40470
34C470
49ABC200470
58X470
68Y470
78Z470
89XYZ210470
99ABC-XYZ470
105X1470
115X2470
1212PQ90180
1312P50180
1412Q40180
1515MD530
1615M240530
1715D110530
1817D160530
1917D270530
2016M150530
"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:
>
|||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:
[vbcol=seagreen]
>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.
>CustIDParentIDConsortiumIDNameConsortiumNameEEsTotalEEs
>14A20
>24B40
>34C
>49ABC200
>58X
>68Y
>78Z
>89XYZ210
>99ABC-XYZ
>105X1
>115X2
>1212PQ90
>1312P50
>1412Q40
>1515MD
>1615M240
>1715D110
>1817D160
>1917D270
>2016M150
>
>Ideally I want something like this.
>CustIDParentIDConsortiumIDNameConsortiumNameEEsTotalEEs
>14A20470
>24B40470
>34C470
>49ABC200470
>58X470
>68Y470
>78Z470
>89XYZ210470
>99ABC-XYZ470
>105X1470
>115X2470
>1212PQ90180
>1312P50180
>1412Q40180
>1515MD530
>1615M240530
>1715D110530
>1817D160530
>1917D270530
>2016M150530
>
>"Steve Kass" wrote:
>
correlated subqueries
Where can I find some good examples of understanding how correlated
subqueries work ?Hi Hassan
How about reading
Inside Microsoft SQL Server 2005 T-SQL Querying ISBN 0735623139
Inside Microsoft SQL Server 2005 Query Tuning and Optimization ISBN 0735621969
John
"Hassan" wrote:
> Where can I find some good examples of understanding how correlated
> subqueries work ?
>
>
subqueries work ?Hi Hassan
How about reading
Inside Microsoft SQL Server 2005 T-SQL Querying ISBN 0735623139
Inside Microsoft SQL Server 2005 Query Tuning and Optimization ISBN 0735621969
John
"Hassan" wrote:
> Where can I find some good examples of understanding how correlated
> subqueries work ?
>
>
Correlated SUB Queries?
I would like to combine the following 3 select statements:
1.
select SECTION_ENGLISH_DESC, D_REGULATION.REG_ENGLISH_DESC, D_SECTION.REG_SURR_ID from D_SECTION INNER JOIN D_REGULATION on D_SECTION.REG_SURR_ID = D_REGULATION.REG_SURR_ID where D_SECTION.reg_surr_id in ('101')
2.
Select count(*) from F_INSPECTIONS where REG_SURR_ID = '101'
3.
select CASE COUNT(*)
WHEN 0 THEN 'Compliant'
ELSE 'Not Compliant'
END
from F_VIOLATIONS
where SECTION_SURR_ID = '201'
the first statement is the main "frame" for what i want to get back. It should loop through all the inspections for 1 regulation (101).
the second statement, i know, is redundant but thats fine. (i get the same number of inspections for the same regulation for each inspection).
The third statement should return weather the current section is compliant (for reg 101). So that example would be for a single section (201) which may be included in reglation 201.
(a regulation has many sections)
Thanks a lot,
Dave Benoiti'm not sure where the correlation comes in
these are uncorrelated subqueries --select SECTION_ENGLISH_DESC
, D_REGULATION.REG_ENGLISH_DESC
, D_SECTION.REG_SURR_ID
, ( select count(*)
from F_INSPECTIONS
where REG_SURR_ID = '101' ) as inspections
, case when
( select count(*)
from F_INSPECTIONS
where SECTION_SURR_ID = '201' ) = 0
then 'Compliant'
else 'Not Compliant' end as compliancies
from D_SECTION
inner
join D_REGULATION
on D_SECTION.REG_SURR_ID
= D_REGULATION.REG_SURR_ID
where D_SECTION.reg_surr_id in ('101')if these really should be correlated, then you can add a WHERE condition to each subquery that references one of the outer tables (D_SECTION or D_REGULATION)|||to clarify
these are not correlated subqueries because the inner query does not reference the outer query with an table alias.|||however, thats exactly what i need to do. In this section:
select count(*)
from F_INSPECTIONS
where SECTION_SURR_ID = '201' ) = 0
then 'Compliant'
else 'Not Compliant' end as compliancies
I actually need to selelct the count(*) of inspections where section_surr_id = the current section_surr_id in the top sql statemtent. (I just hard coded 201 but it should be the current section_id.|||I actually need to selelct the count(*) of inspections where section_surr_id = the current section_surr_id in the top sql statemtent. (I just hard coded 201 but it should be the current section_id.yeah, that was the missing info, wasn't itselect SECTION_ENGLISH_DESC
, D_REGULATION.REG_ENGLISH_DESC
, D_SECTION.REG_SURR_ID
, ( select count(*)
from F_INSPECTIONS
where REG_SURR_ID
= D_SECTION.REG_SURR_ID ) as inspections
, case when
( select count(*)
from F_INSPECTIONS
where SECTION_SURR_ID
= D_SECTION.REG_SURR_ID ) = 0
then 'Compliant'
else 'Not Compliant' end as compliancies
from D_SECTION
inner
join D_REGULATION
on D_SECTION.REG_SURR_ID
= D_REGULATION.REG_SURR_ID
where D_SECTION.reg_surr_id in ('101')if that's not the right correlation, at least now you know how to do it
;) :)|||Hi, Thanks for the answer but im still a bit confused on that same section.
The thing is that I have to find the current inspection number to see if there were any VIOLATIONS (if there was 1 or more VIOLATIONS!!, then its not compliant, otherwise it is considered compliant).
NOTICE the alais currSecID I created in order to make a link between the current SECTION_ID (not regulation_ID) and the one used in the VIOLATIONS Compliant section (above)
select SECTION_ENGLISH_DESC, D_REGULATION.REG_ENGLISH_DESC, D_SECTION.REG_SURR_ID,
D_SECTION.SECTION_SURR_ID currSecID,
( select count(*)
from F_INSPECTIONS
where REG_SURR_ID
= D_SECTION.REG_SURR_ID ) as inspections,
case when
( select count(*)
from F_VIOLATIONS
where SECTION_SURR_ID = currSecID) = 0
then 'Compliant'
else 'Not Compliant' end as compliancies
from D_SECTION
inner
join D_REGULATION
on D_SECTION.REG_SURR_ID
= D_REGULATION.REG_SURR_ID
where D_SECTION.reg_surr_id in ('101')|||don't use the alias in the subquery
1.
select SECTION_ENGLISH_DESC, D_REGULATION.REG_ENGLISH_DESC, D_SECTION.REG_SURR_ID from D_SECTION INNER JOIN D_REGULATION on D_SECTION.REG_SURR_ID = D_REGULATION.REG_SURR_ID where D_SECTION.reg_surr_id in ('101')
2.
Select count(*) from F_INSPECTIONS where REG_SURR_ID = '101'
3.
select CASE COUNT(*)
WHEN 0 THEN 'Compliant'
ELSE 'Not Compliant'
END
from F_VIOLATIONS
where SECTION_SURR_ID = '201'
the first statement is the main "frame" for what i want to get back. It should loop through all the inspections for 1 regulation (101).
the second statement, i know, is redundant but thats fine. (i get the same number of inspections for the same regulation for each inspection).
The third statement should return weather the current section is compliant (for reg 101). So that example would be for a single section (201) which may be included in reglation 201.
(a regulation has many sections)
Thanks a lot,
Dave Benoiti'm not sure where the correlation comes in
these are uncorrelated subqueries --select SECTION_ENGLISH_DESC
, D_REGULATION.REG_ENGLISH_DESC
, D_SECTION.REG_SURR_ID
, ( select count(*)
from F_INSPECTIONS
where REG_SURR_ID = '101' ) as inspections
, case when
( select count(*)
from F_INSPECTIONS
where SECTION_SURR_ID = '201' ) = 0
then 'Compliant'
else 'Not Compliant' end as compliancies
from D_SECTION
inner
join D_REGULATION
on D_SECTION.REG_SURR_ID
= D_REGULATION.REG_SURR_ID
where D_SECTION.reg_surr_id in ('101')if these really should be correlated, then you can add a WHERE condition to each subquery that references one of the outer tables (D_SECTION or D_REGULATION)|||to clarify
these are not correlated subqueries because the inner query does not reference the outer query with an table alias.|||however, thats exactly what i need to do. In this section:
select count(*)
from F_INSPECTIONS
where SECTION_SURR_ID = '201' ) = 0
then 'Compliant'
else 'Not Compliant' end as compliancies
I actually need to selelct the count(*) of inspections where section_surr_id = the current section_surr_id in the top sql statemtent. (I just hard coded 201 but it should be the current section_id.|||I actually need to selelct the count(*) of inspections where section_surr_id = the current section_surr_id in the top sql statemtent. (I just hard coded 201 but it should be the current section_id.yeah, that was the missing info, wasn't itselect SECTION_ENGLISH_DESC
, D_REGULATION.REG_ENGLISH_DESC
, D_SECTION.REG_SURR_ID
, ( select count(*)
from F_INSPECTIONS
where REG_SURR_ID
= D_SECTION.REG_SURR_ID ) as inspections
, case when
( select count(*)
from F_INSPECTIONS
where SECTION_SURR_ID
= D_SECTION.REG_SURR_ID ) = 0
then 'Compliant'
else 'Not Compliant' end as compliancies
from D_SECTION
inner
join D_REGULATION
on D_SECTION.REG_SURR_ID
= D_REGULATION.REG_SURR_ID
where D_SECTION.reg_surr_id in ('101')if that's not the right correlation, at least now you know how to do it
;) :)|||Hi, Thanks for the answer but im still a bit confused on that same section.
The thing is that I have to find the current inspection number to see if there were any VIOLATIONS (if there was 1 or more VIOLATIONS!!, then its not compliant, otherwise it is considered compliant).
NOTICE the alais currSecID I created in order to make a link between the current SECTION_ID (not regulation_ID) and the one used in the VIOLATIONS Compliant section (above)
select SECTION_ENGLISH_DESC, D_REGULATION.REG_ENGLISH_DESC, D_SECTION.REG_SURR_ID,
D_SECTION.SECTION_SURR_ID currSecID,
( select count(*)
from F_INSPECTIONS
where REG_SURR_ID
= D_SECTION.REG_SURR_ID ) as inspections,
case when
( select count(*)
from F_VIOLATIONS
where SECTION_SURR_ID = currSecID) = 0
then 'Compliant'
else 'Not Compliant' end as compliancies
from D_SECTION
inner
join D_REGULATION
on D_SECTION.REG_SURR_ID
= D_REGULATION.REG_SURR_ID
where D_SECTION.reg_surr_id in ('101')|||don't use the alias in the subquery
Correlated SUB Queries?
I would like to combine the following 3 select statements:
1.
select SECTION_ENGLISH_DESC, D_REGULATION.REG_ENGLISH_DESC, D_SECTION.REG_SURR_ID from D_SECTION INNER JOIN D_REGULATION on D_SECTION.REG_SURR_ID = D_REGULATION.REG_SURR_ID where D_SECTION.reg_surr_id in ('101')
2.
Select count(*) from F_INSPECTIONS where REG_SURR_ID = '101'
3.
select CASE COUNT(*)
WHEN 0 THEN 'Compliant'
ELSE 'Not Compliant'
END
from F_VIOLATIONS
where SECTION_SURR_ID = '201'
the first statement is the main "frame" for what i want to get back. It should loop through all the inspections for 1 regulation (101).
the second statement, i know, is redundant but thats fine. (i get the same number of inspections for the same regulation for each inspection).
The third statement should return weather the current section is compliant (for reg 101). So that example would be for a single section (201) which may be included in reglation 201.
(a regulation has many sections)
Thanks a lot,
Dave Benoitplease don't cross-post
http://www.dbforums.com/showthread.php?t=1117027sql
1.
select SECTION_ENGLISH_DESC, D_REGULATION.REG_ENGLISH_DESC, D_SECTION.REG_SURR_ID from D_SECTION INNER JOIN D_REGULATION on D_SECTION.REG_SURR_ID = D_REGULATION.REG_SURR_ID where D_SECTION.reg_surr_id in ('101')
2.
Select count(*) from F_INSPECTIONS where REG_SURR_ID = '101'
3.
select CASE COUNT(*)
WHEN 0 THEN 'Compliant'
ELSE 'Not Compliant'
END
from F_VIOLATIONS
where SECTION_SURR_ID = '201'
the first statement is the main "frame" for what i want to get back. It should loop through all the inspections for 1 regulation (101).
the second statement, i know, is redundant but thats fine. (i get the same number of inspections for the same regulation for each inspection).
The third statement should return weather the current section is compliant (for reg 101). So that example would be for a single section (201) which may be included in reglation 201.
(a regulation has many sections)
Thanks a lot,
Dave Benoitplease don't cross-post
http://www.dbforums.com/showthread.php?t=1117027sql
Correlated query in SELECT clause
There is a problem when I include correlated query in the SELECT clause in
the SQL statement like the following:
SELECT a.column1, (
SELECT b.column2
FROM b
WHERE b.a_id = a.a_id
) as dummy
FROM a;
(just replace a and b with any related table names from your database)
This query executed without any problems in query designer and in a report.
But when executed from inside report designer (Microsoft Developer
Environment, Data tab) I got the following error:
The column prefix 'a' doesn't match with a table name or alias name used in
the query.
Is it some kind of bug or just 'feature' of Query Designer?Try taking out all carraige returns.
VA wrote:
>There is a problem when I include correlated query in the SELECT clause in
>the SQL statement like the following:
>SELECT a.column1, (
> SELECT b.column2
> FROM b
> WHERE b.a_id = a.a_id
>) as dummy
>FROM a;
>(just replace a and b with any related table names from your database)
>This query executed without any problems in query designer and in a report.
>But when executed from inside report designer (Microsoft Developer
>Environment, Data tab) I got the following error:
>The column prefix 'a' doesn't match with a table name or alias name used in
>the query.
>Is it some kind of bug or just 'feature' of Query Designer?
--
Gene Hunter
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-reporting/200508/1|||Does it work if you make it a VIEW, and do SELECT * FROM TheView in
your frontend?
the SQL statement like the following:
SELECT a.column1, (
SELECT b.column2
FROM b
WHERE b.a_id = a.a_id
) as dummy
FROM a;
(just replace a and b with any related table names from your database)
This query executed without any problems in query designer and in a report.
But when executed from inside report designer (Microsoft Developer
Environment, Data tab) I got the following error:
The column prefix 'a' doesn't match with a table name or alias name used in
the query.
Is it some kind of bug or just 'feature' of Query Designer?Try taking out all carraige returns.
VA wrote:
>There is a problem when I include correlated query in the SELECT clause in
>the SQL statement like the following:
>SELECT a.column1, (
> SELECT b.column2
> FROM b
> WHERE b.a_id = a.a_id
>) as dummy
>FROM a;
>(just replace a and b with any related table names from your database)
>This query executed without any problems in query designer and in a report.
>But when executed from inside report designer (Microsoft Developer
>Environment, Data tab) I got the following error:
>The column prefix 'a' doesn't match with a table name or alias name used in
>the query.
>Is it some kind of bug or just 'feature' of Query Designer?
--
Gene Hunter
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-reporting/200508/1|||Does it work if you make it a VIEW, and do SELECT * FROM TheView in
your frontend?
Correlated and aggregate sql query
I have a table similar to the following:
ID | Name ID | Period From | Period To | Percentage |
-----------------------
Important - Each person can have more than one entry.
What I am trying to do is get the last percentage that each person obtained.
The only way I have been able to do this is by the following:
SELECT * FROM myTable
LEFT OUTER JOIN ( SELECT NameID, MAX(PeriodTo) as PeriodTo FROM myTable GROUP BY NameID) t1
ON myTable.NameID = t1.NameID
WHERE myTable.PeriodTo = t1.PeriodTo
I was wondering if there was another way of doing this, or whether this is an efficient method of doing this kind of query. Jagdip
SELECT * FROM myTable where PeriodTo in (select Max(PeriodTo) from myTable group by NameID)
|||This does not work. It finds the rows where the periodTo is equivalent to the maximum periodTo. I need to find the maximum periodTofor each nameID. Its is equivalent toSELECT ID, NameID, ..., MAX(PeriodTo)FROM myTableGROUP BY ID, NameID, ...|||You can use ROW_Number function if you use SQL Server 2005:
SELECT NameID, PeriodFrom, PeriodTo, PercentageFROM(SELECT NameID, PeriodFrom, PeriodTo, Percentage,ROW_Number()OVER(PARTITIONBY NameIDORDERBY PeriodToDESC)as RowNum
FROM mytable)AS t
WHERE RowNum=1
|||Can you post some sample data from the table and expected output? This makes it easier for us to see what should be achieved.
|||if PeriodTo unique then it works
(
SELECT * FROM myTable where PeriodTo in (select Max(PeriodTo) from myTable group by NameID)
in the inner query it selects all the periodto which is max to the nameid >>>then it matches the PeriodTo from the outer query.
select Max(PeriodTo) from myTable group by NameIDthis portion returns the Max PeriodTo for each NameID which is unique
)
Sorry Kamrul, but that is not what I am looking for. Maybe I have not explained the problem properly, so here is some sample data (obviously simplified).
An example of the table is (note that this is british dates - i.e. dd/mm/yy):
ID | NameID | PeriodTo | Comments |---------------------- 1 | Mark | 01/01/01 | ComMark1 | 2 | Mark | 01/06/01 | ComMark2 | 3 | Mark | 01/11/01 | ComMark3 | 4 | Ken | 01/01/01 | ComKen1 | 5 | Ken | 01/06/01 | ComKen2 | 6 | John | 01/01/01 | ComJohn1 |---------------------And the result I am trying to get is :
ID | NameID | PeriodTo | Comments |---------------------- 3 | Mark | 01/11/01 | ComMark3 | 5 | Ken | 01/06/01 | ComKen2 | 6 | John | 01/01/01 | ComJohn |----------------------
Kamrul's code doesn't work because it will return every row.
The code I wrote does return this, but I was wondering whether there was a nice way of doing this kind of correlated aggregated query. I'm looking for the most efficient method. I should also point out that I am using sql server 2000.
Jagdip
|||Hi,
Please try this.
SELECT *FROM MyTable OutMyTable
where PeriodTo = (select Max(PeriodTo)from MyTable InMyTableWHERE InMyTable.NameID=OutMyTable.NameID group by NameID)
Regards,
Gaurang Majithiya
|||For SQL Server 2000:
SELECT NameID, PeriodFrom, PeriodTo, PercentageFROM(
SELECT NameID, PeriodFrom, PeriodTo, Percentage,(SELECTCOUNT(*)FROM mytable aWHERE a.NameID=b.NameIDAND a.PeriodTo>=b.PeriodTo)as RowNum
FROM mytable b)AS t
WHERE RowNum=1
Subscribe to:
Posts (Atom)