December 16, 2008 at 1:57 pm
In the code below. I want to get 1 record in Credentials CTE but it keeps
getting 5, 1 for each record in PracticeSettings CTE.
How can I fix this?
;WITH PracticeSettings (Category, [Name], [Value])
AS (SELECT Category, [Name], [Value]
FROM coPracticeSettings
WHERE Category = 'NCSCRIPTS'),
Credentials (credentials_partnerNameField, credentials_nameField
, credentials_passwordField, credentials_productNameField
, credentials_productVersionField)
AS (SELECT (SELECT [Value] FROM PracticeSettings WHERE [Name] = 'partnername')
, (SELECT [Value] FROM PracticeSettings WHERE [Name] = 'name')
, (SELECT [Value] FROM PracticeSettings WHERE [Name] = 'password')
, (SELECT [Value] FROM PracticeSettings WHERE [Name] = 'productName')
, (SELECT [Value] FROM PracticeSettings WHERE [Name] = 'productVersion')
FROM PracticeSettings
)
December 16, 2008 at 2:08 pm
You aren't joining the two items together.
A CTE behaves as a table. If you have 2, you've defined two tables to use in a query. The query still needs to put a join condition between the tables.
December 16, 2008 at 2:31 pm
Forgot TOP 1
This works...
;WITH PracticeSettings (Category, [Name], [Value])
AS (SELECT Category, [Name], [Value]
FROM coPracticeSettings
WHERE Category = 'NCSCRIPTS'),
Credentials (credentials_partnerNameField, credentials_nameField
, credentials_passwordField, credentials_productNameField
, credentials_productVersionField)
AS (SELECT TOP 1 (SELECT [Value] FROM PracticeSettings WHERE [Name] = 'partnername')
, (SELECT [Value] FROM PracticeSettings WHERE [Name] = 'name')
, (SELECT [Value] FROM PracticeSettings WHERE [Name] = 'password')
, (SELECT [Value] FROM PracticeSettings WHERE [Name] = 'productName')
, (SELECT [Value] FROM PracticeSettings WHERE [Name] = 'productVersion')
FROM PracticeSettings
)
December 16, 2008 at 2:32 pm
Steve Jones - Editor (12/16/2008)
You aren't joining the two items together.A CTE behaves as a table. If you have 2, you've defined two tables to use in a query. The query still needs to put a join condition between the tables.
I'm not sure what you mean here.
Got a better way?
December 16, 2008 at 2:35 pm
So, you have five rows being returned from the first CTE - then, you select all rows from the first CTE in the second CTE and are wondering why you are getting five rows? Is that correct?
There are a couple of ways to resolve this - but, without having sample data available to work with it is just going to be guess work.
Two ideas off the top of my head:
;WITH PracticeSettings (Category, [Name], [Value])
AS (SELECT Category, [Name], [Value]
FROM coPracticeSettings
WHERE Category = 'NCSCRIPTS'),
Credentials (credentials_partnerNameField, credentials_nameField
, credentials_passwordField, credentials_productNameField
, credentials_productVersionField)
AS (SELECT (SELECT [Value] FROM PracticeSettings WHERE [Name] = 'partnername')
, (SELECT [Value] FROM PracticeSettings WHERE [Name] = 'name')
, (SELECT [Value] FROM PracticeSettings WHERE [Name] = 'password')
, (SELECT [Value] FROM PracticeSettings WHERE [Name] = 'productName')
, (SELECT [Value] FROM PracticeSettings WHERE [Name] = 'productVersion')
--FROM PracticeSettings /* don't select from the table and just use the sub-queries above */
)
SELECT *
FROM Credentials;
Or - use a group by to rollup to a single row:
;WITH PracticeSettings (Category, [Name], [Value])
AS (SELECT Category, [Name], [Value]
FROM coPracticeSettings
WHERE Category = 'NCSCRIPTS'),
Credentials (credentials_partnerNameField, credentials_nameField
, credentials_passwordField, credentials_productNameField
, credentials_productVersionField)
AS (SELECT MAX(CASE WHEN [Value] = 'partnername' THEN [Value] END)
, MAX(CASE WHEN [Value] = 'name' THEN [Value] END)
, MAX(CASE WHEN [Value] = 'password' THEN [Value] END)
, MAX(CASE WHEN [Value] = 'productName' THEN [Value] END)
, MAX(CASE WHEN [Value] = 'productVersion' THEN [Value] END)
FROM PracticeSettings
)
SELECT *
FROM Credentials;
There are other ways to go about getting this information without having to use the CTE at all. This really looks like a simple PIVOT query - and if you search this site for PIVOT articles you will find plenty of information on how to get this done.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply