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

Notifications

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

Responsible AI policies

As AI tools become more common, we’re introducing a Responsible AI Use…

Neeraj Kumar – Community Spotlight

We are honored to recognize Neeraj Kumar as our Community Spotlight honoree for…

Congratulations to the November Top 10 Community Leaders!

These are the community rock stars!

Leaderboard > 🔒一 Microsoft Dynamics CRM (Archived)

#1
SA-08121319-0 Profile Picture

SA-08121319-0 4

#1
Calum MacFarlane Profile Picture

Calum MacFarlane 4

#3
Alex Fun Wei Jie Profile Picture

Alex Fun Wei Jie 2

Last 30 days Overall leaderboard

Featured topics

Product updates

Dynamics 365 release plans