Copied to clipboard

Flag this post as spam?

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


  • skiltz 501 posts 701 karma points
    Apr 13, 2010 @ 03:46
    skiltz
    0

    SQL Script for All Members and their Properties

    Does anyone have a SQL script that returns all members and their properties?  I need to be able to bind this to a gridview.  Thanks!

  • Shaun 248 posts 475 karma points
    Jun 16, 2010 @ 17:04
    Shaun
    0

    I too am looking for something like this, can anyone point me in the right direction?

  • ksrao 1 post 21 karma points
    Jun 16, 2010 @ 17:25
    ksrao
    0

    Hi,

     I hope you can get it by using ( just this an rough idea  )

                                                            SELECT  < all The properties what you want>

                                                           FROM   < The Members table> // where the list of users existing// ;

     Here you need not to use WHERE condition as you want to select all members.

     

    For further more SQL questions  <l> http://www.wiziq.com/online-tests/11428-sql-quiz-1 ; </l>

  • Shaun 248 posts 475 karma points
    Jun 16, 2010 @ 17:30
    Shaun
    0

    aah, that only returns the basic member properties, sorry, I should have said, I'm actually after custom member properties.

  • Lee Kelleher 4020 posts 15797 karma points MVP 13x admin c-trib
    Jun 16, 2010 @ 18:09
    Lee Kelleher
    5

    Hey Shaun (and skiltz), are you sure you want it a SQL script for this? Seriously, get ready to open a can-of-worms!!!

    Here's a little something I wrote for a client a year ago...

    SELECT
        mt.pk,
        d.contentNodeId,
        u.text,
        m.Email,
        m.LoginName,
        mt.propertytypeId,
        p.tabId,
        t.text AS tabName,
        p.Alias,
        p.Name,
        p.dataTypeId,
        dt.dbType,
        dt.controlId,
        d.id AS propertyId,
        d.dataInt,
        d.dataDate,
        d.dataNvarchar,
        d.dataNtext,
        mt.viewOnProfile,
        p.sortOrder
    FROM
        dbo.cmsMemberType AS mt INNER JOIN
        dbo.cmsPropertyType AS p ON mt.propertytypeId = p.id INNER JOIN
        dbo.cmsDataType AS dt ON p.dataTypeId = dt.nodeId INNER JOIN
        dbo.cmsTab AS t ON p.tabId = t.id AND mt.NodeId = t.contenttypeNodeId INNER JOIN
        dbo.cmsPropertyData AS d ON p.id = d.propertytypeid INNER JOIN
        dbo.cmsMember AS m ON m.nodeId = d.contentNodeId INNER JOIN
        dbo.umbracoNode AS u ON u.id = m.nodeId

    It's a beast of INNER JOINs, not a big fan of using it... but was forced into it by the client and other technical restrictions at the time.

    Ideally, I'd recommend that you use the ASP.NET Membership Provider API to retrieve member data ... but I'm also realistic, sometimes you need to go for the raw data - just to get the job done.

    Good luck with the SQL script, I can't promise that its going to do what you need it for ... but it's a start.

    Cheers, Lee.

  • Shaun 248 posts 475 karma points
    Jun 16, 2010 @ 19:10
    Shaun
    1

    hehe, cheers Lee. I used something similar in the end.

    It was only for a one-off data grab, so the beastliness of it wasn't really an issue for me.

  • skiltz 501 posts 701 karma points
    Jun 17, 2010 @ 12:53
    skiltz
    1

    This is what I use.

     

    create table myXML (xm_data xml)
    insert
    into MyXML (xm_data)
    select
      cmsContentXml
    .xml from cmsContentXML inner join cmsContent c on c.nodeid = cmsContentXml.nodeid
     
    where c.contentType =  1071
     
    select
    tab
    .col.value('@nodeName','varchar(50)') as 'ID',
    tab
    .col.value('@email','varchar(50)') as 'Email',
    tab
    .col.value('data[@alias][1]','varchar(50)') + ' ' + tab.col.value('data[@alias][2]','varchar(50)') as "Name",
    tab
    .col.value('data[@alias][1]','varchar(50)') as 'First Name',
    tab
    .col.value('data[@alias][2]','varchar(50)') as 'Last Name',
    tab
    .col.value('data[@alias][3]','varchar(50)') as 'Home Phone',
    tab
    .col.value('data[@alias][4]','varchar(50)') as 'Mobile Phone',
    tab
    .col.value('data[@alias][5]','varchar(50)') as 'Address',
    tab
    .col.value('data[@alias][6]','varchar(50)') as 'AvartarMediaID',
    tab
    .col.value('data[@alias][7]','varchar(50)') as 'ShowOnProfile'
    from myXML cross apply xm_data.nodes('node') tab (col)




    drop table
    MyXml
  • Rasmus Østergård 19 posts 81 karma points c-trib
    Sep 08, 2010 @ 13:28
    Rasmus Østergård
    0

    Thanks Lee - you saved me a lot of tedious work

  • Rob Watkins 369 posts 701 karma points
    Jul 09, 2012 @ 17:05
    Rob Watkins
    0

    Can of worms as may be, but it's a very good technique when the client has asked for a list of everyone missing a particular property :o)

    Thanks Lee, worked perfectly.

    For anyone else using MySQL, just lose the dbo.

  • TikTakToe 60 posts 102 karma points
    May 26, 2016 @ 13:17
    TikTakToe
    2

    following on from lee's snippet, slight tweak for later versions (as tab table has gone) and filtering on username

    SELECT        mt.pk, d.contentNodeId, u.text, m.Email, m.LoginName, mt.propertytypeId, p.Alias, p.Name, p.dataTypeId, dt.dbType, d.id AS propertyId, d.dataInt, d.dataDate, d.dataNvarchar, d.dataNtext, mt.viewOnProfile, 
                             p.sortOrder
    FROM            dbo.cmsMemberType AS mt INNER JOIN
                             dbo.cmsPropertyType AS p ON mt.propertytypeId = p.id INNER JOIN
                             dbo.cmsDataType AS dt ON p.dataTypeId = dt.nodeId INNER JOIN
                             dbo.cmsPropertyData AS d ON p.id = d.propertytypeid INNER JOIN
                             dbo.cmsMember AS m ON m.nodeId = d.contentNodeId INNER JOIN
                             dbo.umbracoNode AS u ON u.id = m.nodeId
    WHERE        (m.LoginName = N'[email protected]')
    
  • Bjarne Fyrstenborg 1280 posts 3990 karma points MVP 7x c-trib
    Oct 03, 2018 @ 07:17
    Bjarne Fyrstenborg
    0

    I extended the SQL snippet a bit, so it also included the content type alias.

    SELECT      mt.pk, d.contentNodeId, c.Alias, u.text, m.Email, m.LoginName, mt.propertytypeId, p.Alias, p.Name, p.dataTypeId, dt.dbType, d.id AS propertyId, d.dataInt, d.dataDate, d.dataNvarchar, d.dataNtext, mt.viewOnProfile, 
                             p.sortOrder
    FROM        dbo.cmsMemberType AS mt INNER JOIN
                dbo.cmsPropertyType AS p ON mt.propertytypeId = p.id INNER JOIN
                dbo.cmsDataType AS dt ON p.dataTypeId = dt.nodeId INNER JOIN
                dbo.cmsPropertyData AS d ON p.id = d.propertytypeid INNER JOIN
                dbo.cmsMember AS m ON m.nodeId = d.contentNodeId INNER JOIN
                dbo.umbracoNode AS u ON u.id = m.nodeId INNER JOIN
                dbo.cmsContentType AS c ON c.nodeId = mt.NodeId
    

    So I could use this to query for a specific content type (e.g. a member type):

    SELECT      mt.pk, d.contentNodeId, c.Alias, u.text, m.Email, m.LoginName, mt.propertytypeId, p.Alias, p.Name, p.dataTypeId, dt.dbType, d.id AS propertyId, d.dataInt, d.dataDate, d.dataNvarchar, d.dataNtext, mt.viewOnProfile, 
                             p.sortOrder
    FROM        dbo.cmsMemberType AS mt INNER JOIN
                dbo.cmsPropertyType AS p ON mt.propertytypeId = p.id INNER JOIN
                dbo.cmsDataType AS dt ON p.dataTypeId = dt.nodeId INNER JOIN
                dbo.cmsPropertyData AS d ON p.id = d.propertytypeid INNER JOIN
                dbo.cmsMember AS m ON m.nodeId = d.contentNodeId INNER JOIN
                dbo.umbracoNode AS u ON u.id = m.nodeId INNER JOIN
                dbo.cmsContentType AS c ON c.nodeId = mt.NodeId
    
    WHERE       c.Alias = 'employee' AND (p.Alias = 'company' AND d.dataNvarchar IS NULL)
    

    /Bjarne

  • Tim 168 posts 372 karma points
    Jul 06, 2016 @ 07:51
    Tim
    104

    For anyone still looking for this sort of thing, here's a slightly neater one that produces a table of all the properties in a slightly more usable format, I think you'll need SQL Server 2005+ to use it.

    I'll keep the code updated on my blog: http://thesitedoctor.co.uk/blog/export-umbraco-members-as-csvtable/ but here it is if you want it:

    DECLARE @cols AS NVARCHAR(MAX), @query  AS NVARCHAR(MAX);
    
    SET @cols = STUFF((
                SELECT ',' + QUOTENAME(pt.Name)
                FROM
                    (SELECT [Id] FROM dbo.umbracoNode WHERE nodeObjectType = '9b5416fb-e72f-45a9-a07b-5a9a2709ce43') AS mt
                    LEFT OUTER JOIN (SELECT NodeID, contentType FROM dbo.cmsContent) AS ml ON ml.contentType = mt.id
                    LEFT JOIN dbo.cmsPropertyType AS pt ON pt.contentTypeId = ml.contentType
                GROUP BY pt.sortOrder, ',' + QUOTENAME(pt.Name)
                ORDER BY pt.sortOrder ASC
                FOR XML PATH(''), TYPE
                ).value('.', 'NVARCHAR(MAX)'),1,1,'')
    
    set @query = '
    SELECT
        LoginName,
        Email,
        createDate,
        MemberGroup,
        ' + @cols + '
    FROM
    (
        SELECT
        pt.Name AS MemberFieldName
        ,    ISNULL(CASE
                WHEN dt.DBTYPE = ''Ntext'' THEN CAST(d.[dataNtext] AS NVARCHAR(MAX))
                WHEN dt.DBTYPE = ''Nvarchar'' THEN d.dataNvarchar
                WHEN dt.DBTYPE = ''Date'' THEN CONVERT(nvarchar, d.[dataDate])
                WHEN dt.DBTYPE = ''Integer'' THEN CONVERT(nvarchar, d.[dataInt])
                ELSE NULL
            END, NULL)
            AS MemberData
        ,    m.LoginName
        ,    m.Email
        ,    n.createDate
        ,   g.[Text] AS MemberGroup
        FROM
            (SELECT [Id] FROM dbo.umbracoNode WHERE nodeObjectType = ''9b5416fb-e72f-45a9-a07b-5a9a2709ce43'') AS mt
                LEFT OUTER JOIN (SELECT NodeID, contentType FROM dbo.cmsContent) AS ml ON ml.contentType = mt.id
                LEFT JOIN dbo.cmsPropertyType AS pt ON pt.contentTypeId = ml.contentType
                LEFT JOIN [dbo].[cmsDataType] AS dt ON pt.datatypeID = dt.NodeId
                LEFT JOIN dbo.cmsPropertyData AS d ON d.contentNodeId = ml.NodeID AND d.propertytypeid = pt.id
                LEFT JOIN dbo.cmsMember AS m ON m.NodeID = ml.NodeID
                LEFT JOIN dbo.cmsMember2MemberGroup AS xmg ON xmg.Member = m.NodeID
                LEFT JOIN dbo.umbracoNode AS g ON g.id = xmg.MemberGroup
                LEFT JOIN dbo.umbracoNode AS n ON n.id = m.nodeId
    ) As src
    PIVOT (
        MAX(MemberData)
        FOR MemberFieldName in (' + @cols + ')
    ) aS pvt
    ORDER BY LoginName'
    
    print(@query)
    execute(@query);
    
  • Nino 1 post 71 karma points
    Oct 23, 2016 @ 13:25
    Nino
    0

    thank you Tim

    it works perectly

  • Tim 168 posts 372 karma points
    Oct 23, 2016 @ 19:54
    Tim
    0

    Ah good to hear Nino, you're welcome :)

  • g3t 32 posts 144 karma points
    Feb 25, 2019 @ 17:29
    g3t
    0

    In the script above from Tim how would you include lastlogindate. Thanks

  • John Bergman 483 posts 1132 karma points
    Oct 03, 2018 @ 15:39
    John Bergman
    0

    If you have a LOT of fields in the member record, it doesn't work because they query is longer than what fits in the NVARCHAR(max)

  • mouseball 63 posts 70 karma points
    Apr 24, 2020 @ 02:40
    mouseball
    4

    I've updated Tim's script above to work with Umbraco 8 - theres a minor change that lets you target a specific member type as opposed to all member types (which creates duplicates columns) so first you need to run this little SQL to get the unique ID eg

    select * from dbo.umbracoNode
    where nodeObjectType = '9b5416fb-e72f-45a9-a07b-5a9a2709ce43' 
    

    Then grab the uniqueID for the Member Type you are interested in. Simply modify the script below with your own unique id. Note that I commented out the member groups because I just wanted one row per member and some of my members are in multiple groups

    DECLARE @cols AS NVARCHAR(MAX), @query  AS NVARCHAR(MAX);
    
    SET @cols = STUFF((
                SELECT ',' + QUOTENAME(pt.Name)
                FROM
                    (SELECT [Id] FROM dbo.umbracoNode WHERE uniqueId = 'A062878F-B37B-4B14-A818-E9E994262F18') AS mt
                    LEFT OUTER JOIN (SELECT NodeID, contentTypeId FROM dbo.umbracoContent) AS ml ON ml.contentTypeId = mt.id
                    LEFT JOIN dbo.cmsPropertyType AS pt ON pt.contentTypeId = ml.contentTypeId
    
                GROUP BY pt.Name, pt.sortOrder, ',' + QUOTENAME(pt.Name)
                ORDER BY pt.Name, pt.sortOrder ASC
                FOR XML PATH(''), TYPE
                ).value('.', 'NVARCHAR(MAX)'),1,1,'')
    
    
    set @query = '
    SELECT
        NodeId,
        LoginName,
        Email,
        createDate,
        /*MemberGroup,*/
        ' + @cols + '
    FROM
    (
        SELECT 
        pt.Name AS MemberFieldName
        ,    ISNULL(CASE
                WHEN dt.DBTYPE = ''Ntext'' THEN CAST(d.[textValue] AS NVARCHAR(MAX))
            WHEN dt.DBTYPE = ''Nvarchar'' THEN d.varcharValue
            WHEN dt.DBTYPE = ''Date'' THEN CONVERT(nvarchar, d.[dateValue])
            WHEN dt.DBTYPE = ''Integer'' THEN CONVERT(nvarchar, d.[intValue])
            ELSE NULL
        END, NULL)
        AS MemberData
    ,    m.nodeId
    ,    m.LoginName
    ,    m.Email
    ,    n.createDate
    /*,   g.[Text] AS MemberGroup*/
    FROM
        (SELECT [Id] FROM dbo.umbracoNode WHERE uniqueid = ''A062878F-B37B-4B14-A818-E9E994262F18'') AS mt
            LEFT OUTER JOIN (SELECT NodeID, contentTypeId FROM dbo.umbracoContent) AS ml ON ml.contentTypeId = mt.id
            LEFT JOIN dbo.cmsPropertyType AS pt ON pt.contentTypeId = ml.contentTypeId
            LEFT JOIN [dbo].[umbracoDataType] AS dt ON pt.datatypeID = dt.NodeId
            LEFT JOIN dbo.cmsMember AS m ON m.NodeID = ml.NodeID
            LEFT JOIN dbo.umbracoContentVersion AS cv ON cv.nodeId = m.nodeId
            LEFT JOIN dbo.umbracoPropertyData AS d ON d.versionId = cv.id AND d.propertytypeid = pt.id
            LEFT JOIN dbo.cmsMember2MemberGroup AS xmg ON xmg.Member = m.NodeID
            LEFT JOIN dbo.umbracoNode AS g ON g.id = xmg.MemberGroup
            LEFT JOIN dbo.umbracoNode AS n ON n.id = m.nodeId
    
    ) As src
    PIVOT (
        MAX(MemberData)
        FOR MemberFieldName in (' + @cols + ')
    ) aS pvt
    ORDER BY LoginName'
    
    print(@query)
    execute(@query);
    

    Hope this helps somebody :)

  • Dee 118 posts 338 karma points
    Sep 18, 2021 @ 07:42
    Dee
    0

    @mouseball

    Big thanks! great share! I wonder how I could filter out only the approved members (umbracoMemberApproved = 1)

  • Thomas 315 posts 602 karma points c-trib
    Jan 16, 2023 @ 11:48
    Thomas
    1

    Don't know If any can use this. :)

    I made it for Umbraco 8 and can't fetch custom data, you just need to know the type of field. [varcharValue], [intValue] etc and the alias field

    /****** Get All members  ******/
    SELECT TOP (300000) 
    [dbo].[cmsMember].nodeId
    ,[Node].createDate as createDate
    ,[version].versionDate as UpdateDate
    ,(SELECT TOP (1) [varcharValue] FROM [dbo].[umbracoPropertyData] where propertyTypeId = (SELECT TOP (1)
        pt.id as propertyId
            FROM [dbo].[umbracoContent] 
            Inner JOIN [dbo].[cmsPropertyTypeGroup] as ptg ON [contentTypeId] = ptg.contenttypeNodeId
            Inner JOIN  [dbo].[cmsPropertyType] as pt On ptg.id = propertyTypeGroupId
            where pt.Alias = 'firstname') And versionId = [version].id) as FirstName
    ,(SELECT TOP (1) [varcharValue] FROM [dbo].[umbracoPropertyData] where propertyTypeId = (SELECT TOP (1)
        pt.id as propertyId
            FROM [dbo].[umbracoContent] 
            Inner JOIN [dbo].[cmsPropertyTypeGroup] as ptg ON [contentTypeId] = ptg.contenttypeNodeId
            Inner JOIN  [dbo].[cmsPropertyType] as pt On ptg.id = propertyTypeGroupId
            where pt.Alias = 'lastname') And versionId = [version].id) as LastName
    ,m.Email as Email
    ,(SELECT TOP (1) [varcharValue] FROM [dbo].[umbracoPropertyData] where propertyTypeId = (SELECT TOP (1)
        pt.id as propertyId
            FROM [dbo].[umbracoContent] 
            Inner JOIN [dbo].[cmsPropertyTypeGroup] as ptg ON [contentTypeId] = ptg.contenttypeNodeId
            Inner JOIN  [dbo].[cmsPropertyType] as pt On ptg.id = propertyTypeGroupId
            where pt.Alias = 'landingCodePhoneNumber') And versionId = [version].id) as LandCode
    ,(SELECT TOP (1) [varcharValue] FROM [dbo].[umbracoPropertyData] where propertyTypeId = (SELECT TOP (1)
        pt.id as propertyId
            FROM [dbo].[umbracoContent] 
            Inner JOIN [dbo].[cmsPropertyTypeGroup] as ptg ON [contentTypeId] = ptg.contenttypeNodeId
            Inner JOIN  [dbo].[cmsPropertyType] as pt On ptg.id = propertyTypeGroupId
            where pt.Alias = 'mobileNumber') And versionId = [version].id) as Mobile
    ,(SELECT TOP (1) [varcharValue] FROM [dbo].[umbracoPropertyData] where propertyTypeId = (SELECT TOP (1)
        pt.id as propertyId
            FROM [dbo].[umbracoContent] 
            Inner JOIN [dbo].[cmsPropertyTypeGroup] as ptg ON [contentTypeId] = ptg.contenttypeNodeId
            Inner JOIN  [dbo].[cmsPropertyType] as pt On ptg.id = propertyTypeGroupId
            where pt.Alias = 'streetname') And versionId = [version].id) as StreetName
    ,(SELECT TOP (1) [varcharValue] FROM [dbo].[umbracoPropertyData] where propertyTypeId = (SELECT TOP (1)
        pt.id as propertyId
            FROM [dbo].[umbracoContent] 
            Inner JOIN [dbo].[cmsPropertyTypeGroup] as ptg ON [contentTypeId] = ptg.contenttypeNodeId
            Inner JOIN  [dbo].[cmsPropertyType] as pt On ptg.id = propertyTypeGroupId
            where pt.Alias = 'streetNumber') And versionId = [version].id) as StreetNumber
    ,(SELECT TOP (1) [varcharValue] FROM [dbo].[umbracoPropertyData] where propertyTypeId = (SELECT TOP (1)
        pt.id as propertyId
            FROM [dbo].[umbracoContent] 
            Inner JOIN [dbo].[cmsPropertyTypeGroup] as ptg ON [contentTypeId] = ptg.contenttypeNodeId
            Inner JOIN  [dbo].[cmsPropertyType] as pt On ptg.id = propertyTypeGroupId
            where pt.Alias = 'streetLetter') And versionId = [version].id) as StreetLetter
    ,(SELECT TOP (1) [varcharValue] FROM [dbo].[umbracoPropertyData] where propertyTypeId = (SELECT TOP (1)
        pt.id as propertyId
            FROM [dbo].[umbracoContent] 
            Inner JOIN [dbo].[cmsPropertyTypeGroup] as ptg ON [contentTypeId] = ptg.contenttypeNodeId
            Inner JOIN  [dbo].[cmsPropertyType] as pt On ptg.id = propertyTypeGroupId
            where pt.Alias = 'floor') And versionId = [version].id) as [Floor]
    ,(SELECT TOP (1) [varcharValue] FROM [dbo].[umbracoPropertyData] where propertyTypeId = (SELECT TOP (1)
        pt.id as propertyId
            FROM [dbo].[umbracoContent] 
            Inner JOIN [dbo].[cmsPropertyTypeGroup] as ptg ON [contentTypeId] = ptg.contenttypeNodeId
            Inner JOIN  [dbo].[cmsPropertyType] as pt On ptg.id = propertyTypeGroupId
            where pt.Alias = 'door') And versionId = [version].id) as Door
    ,(SELECT TOP (1) [varcharValue] FROM [dbo].[umbracoPropertyData] where propertyTypeId = (SELECT TOP (1)
        pt.id as propertyId
            FROM [dbo].[umbracoContent] 
            Inner JOIN [dbo].[cmsPropertyTypeGroup] as ptg ON [contentTypeId] = ptg.contenttypeNodeId
            Inner JOIN  [dbo].[cmsPropertyType] as pt On ptg.id = propertyTypeGroupId
            where pt.Alias = 'cOName') And versionId = [version].id) as COName
    ,(SELECT TOP (1) [varcharValue] FROM [dbo].[umbracoPropertyData] where propertyTypeId = (SELECT TOP (1)
        pt.id as propertyId
            FROM [dbo].[umbracoContent] 
            Inner JOIN [dbo].[cmsPropertyTypeGroup] as ptg ON [contentTypeId] = ptg.contenttypeNodeId
            Inner JOIN  [dbo].[cmsPropertyType] as pt On ptg.id = propertyTypeGroupId
            where pt.Alias = 'postalCode') And versionId = [version].id) as PostalCode
    ,(SELECT TOP (1) [varcharValue] FROM [dbo].[umbracoPropertyData] where propertyTypeId = (SELECT TOP (1)
        pt.id as propertyId
            FROM [dbo].[umbracoContent] 
            Inner JOIN [dbo].[cmsPropertyTypeGroup] as ptg ON [contentTypeId] = ptg.contenttypeNodeId
            Inner JOIN  [dbo].[cmsPropertyType] as pt On ptg.id = propertyTypeGroupId
            where pt.Alias = 'city') And versionId = [version].id) as City
    ,(SELECT TOP (1) [varcharValue] FROM [dbo].[umbracoPropertyData] where propertyTypeId = (SELECT TOP (1)
        pt.id as propertyId
            FROM [dbo].[umbracoContent] 
            Inner JOIN [dbo].[cmsPropertyTypeGroup] as ptg ON [contentTypeId] = ptg.contenttypeNodeId
            Inner JOIN  [dbo].[cmsPropertyType] as pt On ptg.id = propertyTypeGroupId
            where pt.Alias = 'country') And versionId = [version].id) as Country
    ,(SELECT TOP (1) [varcharValue] FROM [dbo].[umbracoPropertyData] where propertyTypeId = (SELECT TOP (1)
        pt.id as propertyId
            FROM [dbo].[umbracoContent] 
            Inner JOIN [dbo].[cmsPropertyTypeGroup] as ptg ON [contentTypeId] = ptg.contenttypeNodeId
            Inner JOIN  [dbo].[cmsPropertyType] as pt On ptg.id = propertyTypeGroupId
            where pt.Alias = 'attetion') And versionId = [version].id) as Attetion
    , mg.MemberGroup as GroupId
    ,(Select [text] from [dbo].[umbracoNode] where id = mg.MemberGroup) as GroupName
    ,(SELECT TOP (1) [intValue] FROM [dbo].[umbracoPropertyData] where propertyTypeId = (SELECT TOP (1)
        pt.id as propertyId
            FROM [dbo].[umbracoContent] 
            Inner JOIN [dbo].[cmsPropertyTypeGroup] as ptg ON [contentTypeId] = ptg.contenttypeNodeId
            Inner JOIN  [dbo].[cmsPropertyType] as pt On ptg.id = propertyTypeGroupId
            where pt.Alias = 'nEWSLETTER') And versionId = [version].id) as Marketing
    FROM [dbo].[cmsMember]
    Left JOIN [dbo].[cmsMember2MemberGroup] as mg ON [dbo].[cmsMember].nodeId = mg.Member  
    Left JOIN [dbo].[cmsMember] as m ON [dbo].[cmsMember].nodeId = m.nodeId
    Left JOIN [dbo].[umbracoContentVersion]  as [version] On [dbo].[cmsMember].nodeId = [version].nodeId
    Left JOIN [dbo].[umbracoNode]  as [Node] On [dbo].[cmsMember].nodeId = [Node].id
    
Please Sign in or register to post replies

Write your reply to:

Draft