use result from select as where

Question

use result from select as where

mickdunde

Old Hand

Points: 333
More actions
August 8, 2011 at 5:17 am

#254946

Hi there
I've looking for a way how to use result from a select as a where clause to another statement. As an example I have two tables, tblNodes and tblGroups. tblNodes has two columns, Caption as CustomProperties, while tblGroups has a column named expression.
tblGroups has two rows with the following content:
Nodes.Caption LIKE '%asd%'
Nodes.CustomProperties.Comments = 'asd'
How could I query tblNodes with a where clause which content is the result from a query from tblGroups, so the where clause is added dynamically from tblGroups.
It may be plain simple, but I don't get it atm
Thanks in advance
Christian

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 1

mickdunde (8/8/2011)
Hi there
I've looking for a way how to use result from a select as a where clause to another statement. As an example I have two tables, tblNodes and tblGroups. tblNodes has two columns, Caption as CustomProperties, while tblGroups has a column named expression.
tblGroups has two rows with the following content:
Nodes.Caption LIKE '%asd%'
Nodes.CustomProperties.Comments = 'asd'
How could I query tblNodes with a where clause which content is the result from a query from tblGroups, so the where clause is added dynamically from tblGroups.
It may be plain simple, but I don't get it atm
Thanks in advance
Christian

SELECT ... FROM tblNodes WHERE column IN (SELECT ... FROM tblGroups WHERE ...)

SELECT ... FROM tblNodes n

INNER/LEFT JOIN (SELECT ... FROM tblGroups WHERE...) g ON g.column = n.column

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

mickdunde Old Hand Points: 333 More actions · Answer 2

Thanks for reply, but this does not work. I was not clear, the where clause for tblNodes is already stored in tblGroups.expression

------------------------------------------

|Expression |

------------------------------------------

|Nodes.Caption LIKE '%asd%' |

------------------------------------------

|Nodes.CustomProperties.Comments = 'asd' |

------------------------------------------

I know in () but it's not a result stored in it rather than the where itself.

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 3

Do you mean, construct the WHERE clause programatically client-side?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

mickdunde Old Hand Points: 333 More actions · Answer 4

exactly. I know how to do this in c# or other, but is it possible with tsql?

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 5

Of course. Can you post the parts of the statement you wish to create, with a little more explanation?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

mickdunde Old Hand Points: 333 More actions · Answer 6

Sure. Let's start with the query in the tblGroup table, called ContainerMemberDefinitions in my production system:

SELECT Expression

FROM [ContainerMemberDefinitions]

where ContainerID = 15

This gives me the following result

---------------------------

Nodes.Caption LIKE '%adsl%'

---------------------------

Nodes.CustomProperties.Comments = 'DSL'

---------------------------

I would like this result as a where clause for the following query:

select Nodes.Caption

from dbo.Nodes

where Nodes.Status = 2

and (....)

The part (...) should be replaced with the values from the first query so it would be like this:

select Nodes.Caption

from dbo.Nodes

where Nodes.Status = 2

and (Nodes.Caption LIKE '%adsl%' OR CustomProperties.Comments = 'DSL')

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 7

How do you know the two rows from the first query are ORred and not ANDed?

Do you have any restrictions over how you execute the SQL i.e. can you create a stored procedure and call it from your client?

What object is 'customproperties' referenced below?

select Nodes.Caption

from dbo.Nodes

where Nodes.Status = 2

and (Nodes.Caption LIKE '%adsl%' OR CustomProperties.Comments = 'DSL')

Finally, do you have any control over the ERD? Is the database design your own?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

mickdunde Old Hand Points: 333 More actions · Answer 8

It's ORed, I just verified from the application. It's solarwinds database, so unfortunately no, I cannot make any changes to the ERD. However I can make a stored proc, this should not interfer with solarwinds itself (as long as I don't overwrite one from them).

The "CustomProperties" itself... well I don't know, maybe it's a reference to their application code, but the table Nodes has a column called Comments, and with CustomProperties stripped the query works as it should.

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 9

This will show you a way to do it.

-- test table

DROP TABLE #ContainerMemberDefinitions

CREATE TABLE #ContainerMemberDefinitions (ContainerID INT, Expression VARCHAR(MAX))

INSERT INTO #ContainerMemberDefinitions (ContainerID, Expression)

SELECT 15, '#Nodes.Caption LIKE ''%adsl%''' UNION ALL

SELECT 15, '#Nodes.CustomProperties.Comments = ''DSL''' UNION ALL

SELECT 16, '1 = 1'

-- test table

DROP TABLE #Nodes

CREATE TABLE #Nodes (Caption VARCHAR(15), Comments VARCHAR(15))

INSERT INTO #Nodes (Caption, Comments)

SELECT 'xadslx', 'DSL' UNION ALL

SELECT 'yadsly', 'DSL' UNION ALL

SELECT 'yadsly', '_' UNION ALL

SELECT 'not me', '_'

---------------------------

DECLARE @ContainerID INT, @EXPRESSION VARCHAR(MAX), @Statement VARCHAR(MAX)

SET @ContainerID = 15

SET @EXPRESSION = ''

-- get conditionals for this containerID into a string

SELECT @EXPRESSION = @EXPRESSION + ' OR ' + Expression

FROM #ContainerMemberDefinitions

WHERE ContainerID = 15

SET @EXPRESSION = STUFF(@EXPRESSION,1,4,'') -- remove first 4 characters from 'conditionals string'

SET @EXPRESSION = REPLACE(@EXPRESSION, 'CustomProperties.', '') -- remove 'CustomProperties.'

SET @Statement = 'SELECT * FROM #Nodes WHERE ' + @EXPRESSION -- construct string

PRINT @Statement -- check string

EXEC(@Statement) -- passing parameter @ContainerID int into stored procedure should be safe from sql injection

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden