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 945 posts 2077 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 945 posts 2077 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
Please Sign in or register to post replies

Write your reply to:

Draft