Sign in Register
Copied to clipboard

Flag this post as spam?

This post will be reported to the moderators as potential spam to be looked at


  • Nigel Wilson 939 posts 2061 karma points
    Dec 17, 2009 @ 03:45
    Nigel Wilson
    0

    SQL Query to get Member, MemberGroup and MemberType

    Hi there

    Currently I have the following SQL query thaR outputs all members in a group:

    SELECT cmsMember.nodeId AS Memberid, umbracoNode.text AS Name, 
    cmsMember.Email AS Email, cmsMember.LoginName AS LoginName
    FROM cmsMember 
    INNER JOIN umbracoNode ON cmsMember.nodeId = umbracoNode.id 
    INNER JOIN cmsMember2MemberGroup ON cmsMember.nodeId = cmsMember2MemberGroup.Member 
    WHERE cmsMember2MemberGroup.MemberGroup = 1262 
    AND (umbracoNode.text LIKE '%Wilson%' OR cmsMember.Email LIKE '%Wilson%');

    I however now wish to extend this query so as to only list members based on their MemberType.

    I have trawled the database but cannot identify what fields link Members to MemberTypes.

    Can anyone offer any suggestions or know of which fields are related ?

    Thanks

    Nigel

    Copy Link
  • Nigel Wilson 939 posts 2061 karma points
    Dec 17, 2009 @ 04:10
    Nigel Wilson
    1

    Immediately after posting the above I found what I had been searching for !

    The query below was detailed on this URL:

    http://forum.umbraco.org/yaf_postst2196_GetGownload-all-member-data-using-some-flexabal-SLQ-and-umbrac-stats.aspx 

    SELECT  MEMBTYPEID.text                AS MemberTypeName,
            MEMBTYPEID.id AS MemberTypeID                ,
            MEMBLST.nodeId                              ,
            MEMBNODE.TEXT                               ,
            MEMB.LoginName                              ,
            MEMB.Email                                  ,
            CONVERT(SMALLDATETIME, MEMBNODE.createDate) AS SignUpdate
    FROM
            (SELECT id,
                    text
            FROM    dbo.umbracoNode
            WHERE (nodeObjectType = '9b5416fb-e72f-45a9-a07b-5a9a2709ce43')
            ) AS MEMBTYPEID
            LEFT OUTER JOIN
                    (SELECT nodeId,
                            contentType
                    FROM    dbo.cmsContent
                    ) AS MEMBLST
            ON      MEMBLST.contentType = MEMBTYPEID.id
            LEFT OUTER JOIN dbo.cmsMember AS MEMB
            ON      MEMB.nodeId = MEMBLST.nodeId
            LEFT OUTER JOIN dbo.umbracoNode AS MEMBNODE
            ON      MEMBNODE.id = MEMBLST.nodeId

    All good :-)

    Copy Link

  • This forum is in read-only mode while we transition to the new forum.

    You can continue this topic on the new forum by tapping the "Continue discussion" link below.

Please Sign in or register to post replies