Select from select from select

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)

Select from select from select

Author

Topic

trouble2
Constraint Violating Yak Guru

267 Posts

Posted - 2010-11-30 : 07:51:35

This is a nice one again.
I'm building a sp, where I need to filter some stuff.
It works nice, but it does not filter by PropertyValue yet.

This is what I've got so far:

Create PROCEDURE [dbo].[GetUserIDsByProfileProperty]
@PortalID int,
@PropertyName nvarchar(256),
@PropertyValue nvarchar(256)
AS

/* Determine the EntryID for list datatype */
DECLARE @ListTypeID int
SELECT @ListTypeID = [EntryID] from [Lists] where [ListName] = 'DataType' and [Value] = 'List'

/* do the select */
select
up.UserID as userid,
(
Case (ppd.[DataType])
when @ListTypeID then
(SELECT [Text] FROM dbo.vw_Lists
WHERE
([ListName] = @PropertyName)
AND
[Value] = up.PropertyValue
AND
([PortalID] = -1 OR [PortalID] = @PortalID)
)
else up.PropertyValue
end
) as [TheValue]
from
[ProfilePropertyDefinition] ppd
inner join
[UserProfile] up
on
up.PropertyDefinitionID = ppd.PropertyDefinitionID
where
ppd.[PropertyName] = @PropertyName
and
ppd.PortalID= @PortalID

So when I execute with @portalid = 0 and @properyname = 'Country' and @PropertyValue = 'France'

It will return a nice list like this:
UserID TheValue
8 Italy
19 Spain
14 Norway
etc...

So I would like to add something like

And
[TheValue] like '%@PropertyValue%'

But I don't know how...

The secret to creativity is knowing how to hide your sources. (Einstein)

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2010-11-30 : 08:08:46

The simplest would probably be something like this:

SELECT * 
FROM (
	select 
		up.UserID as userid,
		(Case (ppd.[DataType])
			when @ListTypeID then 
				(SELECT	[Text] 
				FROM dbo.vw_Lists WHERE ([ListName] = @PropertyName)
					AND	[Value] = up.PropertyValue
					AND ([PortalID] = -1 OR [PortalID] = @PortalID)
				)
			else up.PropertyValue
		end
		) as [TheValue]
	from [ProfilePropertyDefinition] ppd
		inner join [UserProfile] up
			on up.PropertyDefinitionID = ppd.PropertyDefinitionID 
	where ppd.[PropertyName] = @PropertyName
	and ppd.PortalID= @PortalID
	) AS dt
WHERE [TheValue] = @PropertyValue

And please add [ code ] tags the next time you post source code so that the formatting is preserved :)

- Lumbago

My blog (yes, I have a blog now! just not that much content yet)
-> www.thefirstsql.com

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-11-30 : 08:11:06

select * from
(
select
up.UserID as userid,
(
Case (ppd.[DataType])
when @ListTypeID then
(SELECT [Text] FROM dbo.vw_Lists
WHERE
([ListName] = @PropertyName)
AND
[Value] = up.PropertyValue
AND
([PortalID] = -1 OR [PortalID] = @PortalID)
)
else up.PropertyValue
end
) as [TheValue]
from
[ProfilePropertyDefinition] ppd
inner join
[UserProfile] up
on
up.PropertyDefinitionID = ppd.PropertyDefinitionID
where
ppd.[PropertyName] = @PropertyName
and
ppd.PortalID= @PortalID
) as t
where [TheValue] like '%'+@PropertyValue+'%'

Madhivanan

Failing to plan is Planning to fail

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-11-30 : 08:12:25

Madhivanan

Failing to plan is Planning to fail

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2010-11-30 : 08:17:34

Sniped, except I forgot about the like part:

where [TheValue] like '%'+@PropertyValue+'%'

:)

- Lumbago

My blog (yes, I have a blog now! just not that much content yet)
-> www.thefirstsql.com

trouble2
Constraint Violating Yak Guru

267 Posts

Posted - 2010-11-30 : 08:19:09

Yes, that is what I thought, but it looked like a lot of selects (that's why I named the post...).
So I thought perhaps someone would come up with something better (if at all possible)

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-11-30 : 08:22:18

quote:
Originally posted by trouble2

Yes, that is what I thought, but it looked like a lot of selects (that's why I named the post...).
So I thought perhaps someone would come up with something better (if at all possible)

The derived table appraoch that we showed you is the better one

Madhivanan

Failing to plan is Planning to fail

trouble2
Constraint Violating Yak Guru

267 Posts

Posted - 2010-11-30 : 08:25:52

Allright I'll go with this then, thanks for your input....

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources