How to return multiple values in subquery

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

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

How to return multiple values in subquery

Author

Topic

bdickey
Starting Member

2 Posts

Posted - 2011-11-01 : 03:24:52

The Adventureworks database samples contains a sample called AWDataWarehouseRefresh. I am trying to modify this sample to work with Adventureworks 2008R2 database. One of the query dimensions is called DimReseller. It contains a subquery to return the Phone Number of the seller, but because the subquery returns more than one record, the code contains a Select Top 1 statement, so the result has the same phone number in each row. This SQL code works, but how would I modify this subquery to return the correct phone number for each row in the result set?

SELECT

s.[BusinessEntityID] AS [ResellerKey], --Unique IDENTIFIER in DW
cu.[CustomerID] AS [CustomerID],
dg.[GeographyKey] AS [GeographyKey], --Map DW GeographyKey from related OLTP tables
cu.[AccountNumber] AS [ResellerAlternateKey], --Where Customer.[CustomerType] = N'S'

(SELECT top 1 ph.[PhoneNumber]
FROM [AdventureWorks2008R2].[Person].[PersonPhone] ph

INNER JOIN [AdventureWorks2008R2].[Person].[Person] co ON ph.[BusinessEntityID] = co.[BusinessEntityID]
INNER JOIN [AdventureWorks2008R2].[Person].[BusinessEntityContact] bec ON bec.[PersonID] = co.[BusinessEntityID]
INNER JOIN [Sales].[Customer] cu on cu.CustomerID = bec.BusinessEntityID
INNER JOIN [Sales].[Store] s on s.BusinessEntityID = cu.CustomerID
WHERE s.[BusinessEntityID] = cu.[CustomerID]) AS [Phone],

CASE Survey.[ref].[value](N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/StoreSurvey";
(BusinessType)', N'varchar(10)')
WHEN N'BM' THEN N'Value Added Reseller' --Bike Manufacturer
WHEN N'BS' THEN N'Specialty Bike Shop'
WHEN N'OS' THEN N'Warehouse'
END AS [BusinessType], --Store Demographic XML data.
s.[Name] AS [ResellerName],
Survey.[ref].[value](N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/StoreSurvey";
(NumberEmployees)', N'int') AS [NumberEmployees], --Store Demographic XML data.
CASE Survey.[ref].[value](N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/StoreSurvey";
(BusinessType)', N'varchar(10)')
WHEN N'BM' THEN N'S' --Semi-Annual
WHEN N'BS' THEN N'A' --Annual
WHEN N'OS' THEN N'Q' --Quarterly
END AS [OrderFrequency],
(SELECT MONTH(MAX([OrderDate])) FROM [AdventureWorks2008R2].[Sales].[SalesOrderHeader] WHERE [CustomerID] = cu.[CustomerID]) AS [OrderMonth], --Is this the month of the last order for this store?
(SELECT YEAR(MIN([OrderDate])) FROM [AdventureWorks2008R2].[Sales].[SalesOrderHeader] WHERE [CustomerID] = cu.[CustomerID]) AS [FirstOrderYear], --Determined based on sales?
(SELECT YEAR(MAX([OrderDate])) FROM [AdventureWorks2008R2].[Sales].[SalesOrderHeader] WHERE [CustomerID] = cu.[CustomerID]) AS [LastOrderYear], --Determined based on sales?
Survey.[ref].[value](N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/StoreSurvey";
(Specialty)', N'nvarchar(50)') AS [ProductLine], --Store survey "Specialty"?
a.[AddressLine1] AS [AddressLine1],
a.[AddressLine2] AS [AddressLine2],
Survey.[ref].[value](N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/StoreSurvey";
(AnnualSales)', N'money') AS [AnnualSales], --Store Demographic XML data.
Survey.[ref].[value](N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/StoreSurvey";
(BankName)', N'nvarchar(50)') AS [BankName], --Store Demographic XML data.
Survey.[ref].[value](N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/StoreSurvey";
(AnnualRevenue)', N'money') AS [AnnualRevenue], --Store Demographic XML data.
Survey.[ref].[value](N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/StoreSurvey";
(YearOpened)', N'int') AS [YearOpened] --Store Demographic XML data.
FROM [AdventureWorks2008R2].[Sales].[Customer] cu
INNER JOIN [AdventureWorks2008R2].[Sales].[Store] s
ON cu.[StoreID] = s.[BusinessEntityID]
INNER JOIN [AdventureWorks2008R2].[Sales].[SalesOrderHeader] ca
ON cu.[CustomerID] = ca.[CustomerID]
INNER JOIN [AdventureWorks2008R2].[Person].[Address] a
ON ca.[BillToAddressID] = a.[AddressID]
INNER JOIN [AdventureWorks2008R2].[Person].[BusinessEntityAddress] bea
ON a.AddressID = bea.[AddressID]
INNER JOIN [AdventureWorks2008R2].[Person].[StateProvince] sp
ON a.[StateProvinceID] = sp.[StateProvinceID]
INNER JOIN [AdventureWorks2008R2].[Person].[CountryRegion] cr
ON sp.[CountryRegionCode] = cr.[CountryRegionCode] COLLATE SQL_Latin1_General_CP1_CI_AS
INNER JOIN [AdvWorksDWX].[dbo].[DimGeography] dg
ON a.[City] = dg.[City] COLLATE SQL_Latin1_General_CP1_CI_AS
AND sp.[StateProvinceCode] = dg.[StateProvinceCode] COLLATE SQL_Latin1_General_CP1_CI_AS
AND cr.[CountryRegionCode] = dg.[CountryRegionCode] COLLATE SQL_Latin1_General_CP1_CI_AS
AND a.[PostalCode] = dg.[PostalCode] COLLATE SQL_Latin1_General_CP1_CI_AS
OUTER APPLY s.[Demographics].[nodes](N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/StoreSurvey";
/StoreSurvey') AS [Survey](ref)
--WHERE bea.[AddressTypeID] = 3 -- Main Office
ORDER BY s.[Name];

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-01 : 04:17:51

you want it to return string of phone numbers?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

bdickey
Starting Member

2 Posts

Posted - 2011-11-02 : 01:37:56

I want the subquery to return the correct phone number for each row in the result set. As written, it only returns one phone number for all the rows in the result set. Seems to me that it would have to be a join rather than a subquery, but I can't figure out how to do the join. I want to display the phone number along with all of the other data for each of the resellers.

Thanks,
Bob

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-02 : 05:45:05

quote:
Originally posted by bdickey

I want the subquery to return the correct phone number for each row in the result set. As written, it only returns one phone number for all the rows in the result set. Seems to me that it would have to be a join rather than a subquery, but I can't figure out how to do the join. I want to display the phone number along with all of the other data for each of the resellers.

Thanks,
Bob

i think you're not putting correct where condition. what are columns using which you want to link and retrieve a phone number?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources