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
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy