Solved

SQL statement for dotnetnuke user profile reports

Posted on 2008-10-30
3
471 Views
Last Modified: 2012-05-05
The code snippet provided below works. I am trying to build a report from the typical ASP .NET user profile definitions and I don't know how to go about it.

In the code below I have defined INNER JOIN UserProfile UP on U.Userid = UP.UserID
In this table there is a field to define as  UP.PropertyDEfinitionID
Now depending on the value of this field example:
Possible values:
35 = Company Name
31 = Phone
33 = Fax
... and so on.

THEN I want to add a column name corresponding to the PropertyDEfinitionID value (eg Company) and set the column value to the next field called PropertyValue

So I would assume that the definition of PropertyValue  would be UP.PropertyValue

The outcome of my report would be:
username
firstname
lastname
rolename
email
AND THEN ...
Company
Phone
Fax
AND SO ON  ......


Urgent help required ...




SELECT U.Username, U.Firstname, U.Lastname, R.Rolename, AM.Email 
FROM Users U
INNER JOIN UserRoles UR on U.Userid = UR.Userid
INNER JOIN Roles R on UR.Roleid = R.Roleid
INNER JOIN Aspnet_users AU on U.Username = AU.Username
INNER JOIN Aspnet_membership AM on AU.Userid = AM.Userid
 
INNER JOIN UserProfile UP on U.Userid = UP.UserID

Open in new window

0
Comment
Question by:siacom
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 10

Accepted Solution

by:
calpurnia earned 500 total points
ID: 22846571
Is this the sort of thing you're after?

SELECT U.UserID, (SELECT UP.PropertyValue FROM UserProfile UP WHERE U.UserID=UP.UserID and UP.PropertyDefinitionID=35) AS Company, (SELECT UP.PropertyValue FROM UserProfile UP WHERE U.UserID=UP.UserID and UP.PropertyDefinitionID=31) AS Phone
FROM Users U;
0
 

Author Closing Comment

by:siacom
ID: 31511908
Yes that works fine, thank you.
After posting the question I also resolved it with this statement:
SELECT TOP (100) PERCENT
Company.PropertyValue  AS Company,
dbo.Users.LastName     AS [Last Name],
dbo.Users.FirstName    AS [First Name],
Street.PropertyValue   AS Street,
Suburb.PropertyValue   AS Suburb,
State.PropertyValue    AS State,
Postcode.PropertyValue AS Postcode,
Phone.PropertyValue    AS [Phone],
dbo.Users.Email

FROM dbo.Users INNER JOIN
     dbo.UserProfile AS Street ON dbo.Users.UserID = Street.UserID AND
                        Street.PropertyDefinitionID = '26' LEFT OUTER JOIN
     dbo.UserProfile AS Suburb ON dbo.Users.UserID = Suburb.UserID AND
                        Suburb.PropertyDefinitionID = '27' LEFT OUTER JOIN
     dbo.UserProfile AS State ON dbo.Users.UserID = State.UserID AND
                        State.PropertyDefinitionID = '28' LEFT OUTER JOIN
     dbo.UserProfile AS Postcode ON dbo.Users.UserID = Postcode.UserID AND
                        Postcode.PropertyDefinitionID = '30' LEFT OUTER JOIN
     dbo.UserProfile AS Phone ON dbo.Users.UserID = Phone.UserID AND
                        Phone.PropertyDefinitionID = '31' LEFT OUTER JOIN
     dbo.UserProfile AS Company ON dbo.Users.UserID = Company.UserID AND
                        Company.PropertyDefinitionID = '35'
ORDER BY Phone

both statement work fine.
0
 

Author Comment

by:siacom
ID: 22846646
ONe other question, the statements work well in SQL manager.

I wanted to do a report ion Microsoft Access, and the syntax is not recognised. What would be the equivalent syntax for Ms Access with the same sql tables linked ??

0

Featured Post

Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
What Is an Error? 2 59
Need multiple Group By's 8 55
Shared Service Environment 2 54
Javascript function 3 19
I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…

734 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question