Page 2 Need help with SQL query - General Discussion

New Community Website

Ordinarily, you'd be at the right spot, but we've recently launched a brand new community website... For the community, by the community.

Yay... Take Me to the Community!

Welcome to the DNN Community Forums, your preferred source of online community support for all things related to DNN.
In order to participate you must be a registered DNNizen

Home All Forums

1
2
3
4
5

Home

Our Community

General Discuss...

Need help with SQL query

8/31/2007 6:43 AM

duchoangle

www.6h15.com
Joined: 6/22/2005

Posts: 457

Re: Need help with SQL query

Thanks, both of you.

BTW, I remember seeing a tip that allows newly registered users to be added automatically to the Link module. Does anyone know this? It'll be a very handy technique.

8/31/2007 9:14 AM

Sebastian Leupold

www.dnnwerk.de
Joined: 3/8/2004

Posts: 46212

Re: Need help with SQL query

I am not aware of such a solution and suggest to have a look at free User Directory from www.effority.net

Cheers from Germany,
Sebastian Leupold

Speed up your DNN Websites with TurboDNN

8/31/2007 11:54 AM

Ed DeGagne

www.southvillagesoftware.com
Joined: 12/2/2003

Posts: 420

Re: Need help with SQL query

kjparker wrote

SELECT     dbo.Users.UserID, dbo.Users.Email, dbo.Users.DisplayName,
                          (SELECT     propertyvalue
                            FROM          userprofile
                            WHERE      propertydefinitionid = 27 AND userid = dbo.users.userid) AS City,
                          (SELECT     propertyvalue
                            FROM          userprofile
                            WHERE      propertydefinitionid = 35 AND userid = dbo.users.userid) AS IM, dbo.UserPortals.PortalId
FROM         dbo.Users INNER JOIN
                      dbo.UserPortals ON dbo.Users.UserID = dbo.UserPortals.UserId
WHERE     (dbo.UserPortals.PortalId = 2)

SELECT     dbo.Users.UserID, dbo.Users.Email, dbo.Users.DisplayName,
                          (SELECT     propertyvalue
                            FROM          userprofile WITH (NOLOCK)
                            WHERE      propertydefinitionid = 27 AND userid = dbo.users.userid) AS City,
                          (SELECT     propertyvalue
                            FROM          userprofile WITH (NOLOCK)
                            WHERE      propertydefinitionid = 35 AND userid = dbo.users.userid) AS IM, dbo.UserPortals.PortalId
FROM         dbo.Users WITH (NOLOCK)

INNER JOIN
dbo.UserPortals WITH (NOLOCK) ON dbo.Users.UserID = dbo.UserPortals.UserId
WHERE (dbo.UserPortals.PortalId = 2)

Ed DeGagne
South Village Software

8/31/2007 11:09 PM

duchoangle

www.6h15.com
Joined: 6/22/2005

Posts: 457

Re: Need help with SQL query

Thanks Ed,

Actually, I do know the Effority User directory but as I've mentioned, it's not compatible with DNN 3.3.7

What I need is a LISTING module that allows us to choose which profile fields to display. Does such module exist?

Regards,
Duc

Page 2 of 2

Need help with SQL query

These Forums are dedicated to discussion of DNN Platform and Evoq Solutions.

For the benefit of the community and to protect the integrity of the ecosystem, please observe the following posting guidelines:

No Advertising. This includes promotion of commercial and non-commercial products or services which are not directly related to DNN.
No vendor trolling / poaching. If someone posts about a vendor issue, allow the vendor or other customers to respond. Any post that looks like trolling / poaching will be removed.
Discussion or promotion of DNN Platform product releases under a different brand name are strictly prohibited.
No Flaming or Trolling.
No Profanity, Racism, or Prejudice.
Site Moderators have the final word on approving / removing a thread or post or comment.
English language posting only, please.

Products

Solutions

Resources

Partners

Community

Blog

About

QA

Ideas Test

New Community Website

DNN Corp. (DotNetNuke)