Subquery reference Outer query? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Subquery reference Outer query?

Author

Topic

Blip
Starting Member

5 Posts

Posted - 2012-03-14 : 05:02:00

Hi All,

I have a nested query that needs to reference a field from the outer query. (SLCustomerAccount.CustomerNumber - Highlighted in red below) Can anyone give me an idea how to do this?

Here's my SQL

SELECT SLCustomerAccount.CustomerAccountNumber, SLCustomerAccount.CustomerAccountName, SYSTraderAnalysisValue.Name as BusinessType, (SELECT SYSTraderAnalysisValue.Name
FROM SLCustomerAnalysisHeadValue INNER JOIN SLCustomerAccount ON SLCustomerAnalysisHeadValue.SLCustomerAccountID = SLCustomerAccount.SLCustomerAccountID INNER JOIN SYSTraderAnalysisValue ON
SLCustomerAnalysisHeadValue.SYSTraderAnalysisValueID = SYSTraderAnalysisValue.SYSTraderAnalysisValueID
WHERE (SLCustomerAnalysisHeadValue.SYSTraderAnalysisHeadingID = 1062 AND (SLCustomerAccount.CustomerAccountNumber= '00000003'))) as Representative
FROM SLCustomerAnalysisHeadValue INNER JOIN
SLCustomerAccount ON SLCustomerAnalysisHeadValue.SLCustomerAccountID = SLCustomerAccount.SLCustomerAccountID INNER JOIN
SYSTraderAnalysisValue ON
SLCustomerAnalysisHeadValue.SYSTraderAnalysisValueID = SYSTraderAnalysisValue.SYSTraderAnalysisValueID
WHERE (SLCustomerAnalysisHeadValue.SYSTraderAnalysisHeadingID = 1385)

The '00000003' needs to reference the result of SLCustomerAccount.CustomerAccountNumber

Thanks for any help.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2012-03-14 : 05:35:16

use different alias names for your tables

No, you're never too old to Yak'n'Roll if you're too young to die.

Blip
Starting Member

5 Posts

Posted - 2012-03-14 : 05:39:14

They are different

as BusinessType

as Representative

?

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2012-03-14 : 05:48:03

use different alias names for your tables not columns

No, you're never too old to Yak'n'Roll if you're too young to die.

Blip
Starting Member

5 Posts

Posted - 2012-03-14 : 06:34:22

Resolved

Many thanks for your help

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2012-03-14 : 06:44:21

Here is the easy rewrite

SELECT		ca.CustomerAccountNumber,
		ca.CustomerAccountName,
		av.Name AS BusinessType,
		(
			SELECT		z.Name
			FROM		dbo.SLCustomerAnalysisHeadValue AS x
			INNER JOIN	dbo.SLCustomerAccount AS y ON y.SLCustomerAccountID = x.SLCustomerAccountID
			INNER JOIN	dbo.SYSTraderAnalysisValue AS z ON z.SYSTraderAnalysisValueID = x.SYSTraderAnalysisValueID
			WHERE		x.SYSTraderAnalysisHeadingID = 1062
					AND ca.CustomerAccountNumber = '00000003'
		) AS Representative
FROM		dbo.SLCustomerAnalysisHeadValue AS hv
INNER JOIN	dbo.SLCustomerAccount AS ca ON ca.SLCustomerAccountID = hv.SLCustomerAccountID
INNER JOIN	dbo.SYSTraderAnalysisValue AS av ON av.SYSTraderAnalysisValueID = hv.SYSTraderAnalysisValueID
WHERE		hv.SYSTraderAnalysisHeadingID = 1385

N 56°04'39.26"
E 12°55'05.63"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2012-03-14 : 06:48:12

And here is one for performance

SELECT		MAX(CASE WHEN hv.SYSTraderAnalysisHeadingID = 1385 THEN ca.CustomerAccountNumber ELSE NULL END) AS CustomerAccountNumber,
		MAX(CASE WHEN hv.SYSTraderAnalysisHeadingID = 1385 THEN ca.CustomerAccountName ELSE NULL END) AS CustomerAccountName,
		MAX(CASE WHEN hv.SYSTraderAnalysisHeadingID = 1385 THEN av.Name ELSE NULL END) AS BusinessType,
		MAX(CASE WHEN hv.SYSTraderAnalysisHeadingID = 1062 AND ca.CustomerAccountNumber = '00000003' THEN av.Name ELSE NULL END) AS Representative
FROM		dbo.SLCustomerAnalysisHeadValue AS hv
INNER JOIN	dbo.SLCustomerAccount AS ca ON ca.SLCustomerAccountID = hv.SLCustomerAccountID
INNER JOIN	dbo.SYSTraderAnalysisValue AS av ON av.SYSTraderAnalysisValueID = hv.SYSTraderAnalysisValueID
WHERE		hv.SYSTraderAnalysisHeadingID IN (1062, 1385)

N 56°04'39.26"
E 12°55'05.63"

Blip
Starting Member

5 Posts

Posted - 2012-03-14 : 08:56:50

Thanks SwePeso

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2012-03-14 : 11:19:22

Did you notice a performance gain?

N 56°04'39.26"
E 12°55'05.63"

Blip
Starting Member

5 Posts

Posted - 2012-03-15 : 05:18:27

Yes considerable

Many thanks for the help

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2012-03-15 : 05:20:36

Any numbers to share to us?

N 56°04'39.26"
E 12°55'05.63"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources