web
You’re offline. This is a read only version of the page.
close
Skip to main content

Announcements

No record found.

News and Announcements icon
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Forums / Microsoft Dynamics CRM (Archived) / SQL Query
Microsoft Dynamics CRM (Archived)

SQL Query

(0) ShareShare
ReportReport
Posted on by LiveBomb 710

Hello,

can anybody say me which tables should I use, when I want select contacts owned by CRMuser which are not in any marketing list?

I can write SQL query, but I do not know CRM DB structure.

*This post is locked for comments

I have the same question (0)
  • Community Member Profile Picture
    Community Member on at

    why are you using SQL queries , instead you can try advanced find frm contacts

    there you can select Contacts Owner by staff member or members then related entity list you can see Marketing List then Marketing list type  contact

  • LiveBomb Profile Picture
    LiveBomb 710 on at

    It can not be find with advanced search. If you set related entity marketing list, then it supposed at least one marketing list is associated to the contact.

    Can be searched contacnts owned by user which are in (anyone) marketing list but can not search contacts which are not in any list.

  • Verified answer
    Community Member Profile Picture
    Community Member on at

    Marketing List records table is call 'List'

    and Marketing List Members in table "ListMember'

    this is not  Guarantee but you can try

    select contactId  from  contact where statecode=0

    except

    select EntityId from ListMember  where entitytype=2   -- 2 indicate contact

    this will give you all live contacts Ids except the once in List members

    so to get fields you need

    you can try select emailaddress, fullname .......<fieldnames> from contacts where contactId in (<above sql>)

    try this as these are select sqls no harm to your crm database.

  • Verified answer
    Community Member Profile Picture
    Community Member on at

    This is pretty rudimentary, but should expose what you're looking for....

    SELECT * FROM Contact WHERE ContactId IN (SELECT EntityId FROM ListMember)

  • Suggested answer
    Community Member Profile Picture
    Community Member on at

    Hi,

    Just to add always use Filtered Views (auto created for each entity) instead of querying tables directly, as it will bypass the Security Role of the user. Use FilteredContact, FilteredList & FilteredListMember views.

    Thanks,

    Gopal Kanojiya

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Introducing the 2026 Season 1 community Super Users

Congratulations to our 2026 Super Stars!

Congratulations to our 2025 Community Spotlights

Thanks to all of our 2025 Community Spotlight stars!

Congratulations to the February Top 10 Community Leaders

These are the community rock stars!

Leaderboard > 🔒一 Microsoft Dynamics CRM (Archived)

#1
JS-09031509-0 Profile Picture

JS-09031509-0 3

#2
AS-17030037-0 Profile Picture

AS-17030037-0 2

#2
Mark Eckert Profile Picture

Mark Eckert 2

Last 30 days Overall leaderboard

Featured topics

Product updates

Dynamics 365 release plans