Multiple selects to get some values

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)

Multiple selects to get some values

Author

Topic

trouble2
Constraint Violating Yak Guru

267 Posts

Posted - 2009-11-05 : 10:42:17

Hi, I made this SP which get's user information from a user but some user profile fields are stored in a different table.
So my sp now looks like this, only I think it must be possible to do this smarter:

SELECT
Users.FirstName,
Users.LastName,
Users.Username,
Users.Email,
Users.DisplayName,
Country =
(
SELECT
UserProfile.PropertyValue
FROM
UserProfile INNER JOIN
ProfilePropertyDefinition
ON
UserProfile.PropertyDefinitionID = ProfilePropertyDefinition.PropertyDefinitionID
where
ProfilePropertyDefinition.PropertyName = 'country'
And
ProfilePropertyDefinition.portalid = 0
And
UserProfile.userid = Users.UserID
),
MemberNumber = (
(
SELECT
UserProfile.PropertyValue
FROM
UserProfile INNER JOIN
ProfilePropertyDefinition
ON
UserProfile.PropertyDefinitionID = ProfilePropertyDefinition.PropertyDefinitionID
where
ProfilePropertyDefinition.PropertyName = 'MemberNumber'
And
ProfilePropertyDefinition.portalid = 0
And
UserProfile.userid = Users.UserID
)
)

FROM Users INNER JOIN
UserRoles ON Users.UserID = UserRoles.UserID INNER JOIN
Roles ON UserRoles.RoleID = Roles.RoleID
WHERE
(Roles.RoleName = 'NNNN')
AND
(Roles.PortalID = 0)
AND
(Users.IsDeleted = 0)

trouble2
Constraint Violating Yak Guru

267 Posts

Posted - 2009-11-06 : 08:13:53

To difficult?

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

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2009-11-06 : 08:49:17

http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
Jim

Everyday I learn something that somebody else already knew

trouble2
Constraint Violating Yak Guru

267 Posts

Posted - 2009-11-06 : 08:59:50

Well, everytime I want to get a value for some profilefield I have to do this:

[FIELDNAME] =
(
SELECT
UserProfile.PropertyValue
FROM
UserProfile INNER JOIN
ProfilePropertyDefinition
ON
UserProfile.PropertyDefinitionID = ProfilePropertyDefinition.PropertyDefinitionID
where
ProfilePropertyDefinition.PropertyName = '[FIELDNAME]'
And
ProfilePropertyDefinition.portalid = 0
And
UserProfile.userid = Users.UserID
),

So I asked if perhaps there is a smarter way of doing this.
Perhaps I call all profilefields I want in 1 subquery.
Or I have to make another sp which contains the subquery.
Or perhaps I can put it in a function.

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

asgast
Posting Yak Master

149 Posts

Posted - 2009-11-06 : 09:16:15

Yes there but we hide it :)

it would be much better just to join UserProfile and select UserProfile.PropertyValue, currently all your subselects run for every row in your result set.

Can't fall asleep, the ghost of sql server will get me for writing bad queries. Have to stay awake ...

trouble2
Constraint Violating Yak Guru

267 Posts

Posted - 2009-11-06 : 10:13:52

No that's not possible because of the structure of the data.

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

Ifor
Aged Yak Warrior

700 Posts

Posted - 2009-11-06 : 10:17:12

This looks like an EAV (Entity-attribute-value) database design which should only be used for semi-structured data.
(Even then XML is probably better.)

If you cannot alter the db schema and just want to get a result, something like the following should work:


SELECT
	U.FirstName
	,U.LastName
	,U.Username
	,U.Email
	,U.DisplayName
	,UP.Country
	,UP.MemberNumber
FROM Users U
	LEFT JOIN
	(
		SELECT UP1.userid
			,MAX
			(
				CASE
					WHEN PPD.PropertyName = 'Country'
					THEN UP1.PropertyValue
				END
			) AS Country
			,MAX
			(
				CASE
					WHEN PPD.PropertyName = 'MemberNumber'
					THEN UP1.PropertyValue
				END
			) AS MemberNumber
		FROM UserProfile UP1
			JOIN ProfilePropertyDefinition PPD
				ON UP1.PropertyDefinitionID = PPD.PropertyDefinitionID
		WHERE PPD.portalid = 0
			AND PPD.PropertyName IN ('Country', 'MemberNumber')
		GROUP BY UP1.userid
	) UP
		ON U.userid = UP.userid
WHERE U.IsDeleted = 0
	AND EXISTS
	(
		SELECT *
		FROM UserRoles UR
		WHERE UR.UserId = UR.UserID
			AND EXISTS
			(
				SELECT *
				FROM Roles R
				WHERE R.RoleID = UR.RoleID
					AND R.RoleName = 'NNNN'
					AND R.PortalID = 0
			)
	)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources