Hi all

I hope I can get some help to make a query for users on my dnn4.9 site.

I would like to get an extract like

Username
Displayname,
Firstname
Lastname
Street
City
Zipcode
email
Phonenumber

I'm no wizard in SQL and I got completly stuck in the properties and the join relations there.

Can anybody assist here?

Thanks
Henry

Henry-
You didn't mention anything about user roles / security groups.  Do you really want this info for every account on your portal?  For example, you may want to exclude users in the ADMINISTRATOR role or exclude users who never logged in except for the day the account was created (i.e., users who created an account but never returned).

DNN Object Classes
It's often much easier to get this kind of info via the exposed attributes of appropriate DNN code objects than to write your own queries.  The DNN framework has already done all the hard work in linking together the appropriate DB tables to bring you those consolidated code objects.

Since you've specifically asked for a query I'll assume you're either creating a report external to DNN or are using a "SQL module" to report the results within your DNN site.  If you're not familiar with "SQL modules", there are a couple of good free ones I've used a lot and have blogged about here:

  Advanced DataGrid (Part 1):  http://www.eguanasolutions.com/DNN_Blog/EntryID/2.aspx
  Advanced DataGrid (Part 2):  http://www.eguanasolutions.com/DNN_Blog/EntryID/5.aspx
  Advanced DataGrid (Part 3):  http://www.eguanasolutions.com/DNN_Blog/EntryID/13.aspx
  SQLView:  http://www.eguanasolutions.com/DNN_Blog/EntryID/15.aspx

If, however, you are using the query in order to populate something in code then you'd likely do better to check into the object classes provided by DNN. Here's a potentially helpful link:  http://www.howtodotnetnuke.com/tabid/120/smid/481/ArticleID/7/t/Getting-Aquainted-with-the-DNN-Classes/Tutorials.aspx

SQL Query 
All that said, here's one stab at the query you asked for.  This is a standalone query and does not leverage existing DNN SPs or functions.  I added a column for "PortalId" in case you have more than one portal and I sorted the results by portal-lastname-firstname.  Please note that this is an inefficient way to retrieve this info but I thought the arrangement would help illustrate relationships between the USER and USER PROFILE-related tables:

  declare @pid int;
  set @pid = (Your portal's ID value here);  -- Use '0' if you haven't created multiple portals 
  SELECT
    upo.PortalId, u.Username, u.DisplayName, u.FirstName, u.LastName, u.Email,
    (select upr1.PropertyValue from UserProfile upr1
      INNER JOIN ProfilePropertyDefinition ppd1 on
      ppd1.PropertyDefinitionId = upr1.PropertyDefinitionId
     where
      (ppd1.portalid = @pid) and (PropertyCategory = 'Address')
       and (PropertyName = 'Street') and (upr1.userid = u.userid)
     ) as [Street],
    (select upr1.PropertyValue from UserProfile upr1 
      INNER JOIN ProfilePropertyDefinition ppd1 on 
      ppd1.PropertyDefinitionId = upr1.PropertyDefinitionId
     where
      (ppd1.portalid = @pid) and (PropertyCategory = 'Address')
       and (PropertyName = 'City') and (upr1.userid = u.userid)
     ) as [City],
    (select upr1.PropertyValue from UserProfile upr1 
      INNER JOIN ProfilePropertyDefinition ppd1 on 
      ppd1.PropertyDefinitionId = upr1.PropertyDefinitionId
     where
      (ppd1.portalid = @pid) and (PropertyCategory = 'Address')
       and (PropertyName = 'PostalCode') and (upr1.userid = u.userid)
     ) as [ZIP],
    (select upr1.PropertyValue from UserProfile upr1 
      INNER JOIN ProfilePropertyDefinition ppd1 on 
      ppd1.PropertyDefinitionId = upr1.PropertyDefinitionId
     where
      (ppd1.portalid = @pid) and (PropertyCategory = 'Contact Info')
       and (PropertyName = 'Telephone') and (upr1.userid = u.userid)
     ) as [Telephone]
   
   FROM USERS u INNER JOIN UserPortals upo on upo.UserId = u.UserId
   WHERE upo.portalid = @pid
   ORDER BY LastName, FirstName

 
Have fun-
-mamlin

Hi again mamlin

Do you think you could help me out on the inRole = "something"?

regards
Henry