Looking for a view of all Account Names against a mailing List RRS feed

  • Question

  • I am trying to write an SQL statement that links a Mailing List in Dynamics CRM to each account. Showing all accounts associated with the mailing list.

    I have so far linked dbo.list to dbo.listmembers which shows me Listmember ID but I am unable to get the name of the account against this.

    Any ideas would be appreciated

    Wednesday, December 10, 2014 4:53 PM

All replies

  • You can join on the entityid column of listmember

    select l.listname, a.name as Accountname
    from filteredlist l
    join filteredlistmember lm on l.listid = l.listid
    join filteredaccount a on lm.entityid = a.accountid

    Microsoft CRM MVP - http://mscrmuk.blogspot.com/ http://www.excitation.co.uk

    Wednesday, December 10, 2014 5:37 PM
  • Hey David,

    What i ended up doing was as follows:

    SELECT List.ListName, Account.Name FROM Account
    LEFT JOIN ListMember 
    ON Account.accountid=ListMember.EntityId
    LEFT JOIN List
    ON ListMember.listid=List.listid
    WHERE ListName = '';

    Only just saw your post but thank you for your help

    Thursday, December 11, 2014 2:40 PM