Hi,

SELECT Comp_Name,comp_sector,pers_firstname,pers_lastname,pers_title,emai_emailaddress
FROM Company
INNER JOIN Person on pers_companyId = comp_companyId AND pers_deleted IS NULL
LEFT JOIN EmailLink on elink_recordId = pers_personId AND elink_entityId = 13 AND elink_deleted IS NULL
LEFT JOIN Email on Emai_EmailId = ELink_EmailId AND emai_deleted IS NULL
WHERE comp_deleted IS NULL

Will return companies that have people associated to them only, and looks at all email addresses, so this would return a record for each email address in the system, e.g.

You could use the following to only look at a specific type of email address, e.g. business:

SELECT Comp_Name,comp_sector,pers_firstname,pers_lastname,pers_title,emai_emailaddress
FROM Company
INNER JOIN Person on pers_companyId = comp_companyId AND pers_deleted IS NULL
LEFT JOIN EmailLink on elink_recordId = pers_personId AND elink_entityId = 13 AND elink_deleted IS NULL AND elink_type = 'Business'
LEFT JOIN Email on Emai_EmailId = ELink_EmailId AND emai_deleted IS NULL
WHERE comp_deleted IS NULL

If you want all companies regardless of if they have people associated, change the INNER JOIN to a LEFT JOIN.

I am not sure what you need this view for, but it is a good idea to include all the advised fields for each entity used, the warning CRM gives is:

Any SELECT statements on Primary Entities in this view must also retrieve the _secterr, _createdby, _channelId and _primaryUserId(or_assignedUserId) columns for EACH primary entity referenced in this view. This can be done by selecting the column explicitly (e.g. select comp_name, comp_companyid, comp_secterr from Company) OR by selecting ALL columns from the Primary Entities in question. Failure to ensure that these primary entity columns are present in this view may result in SQL errors in the CRM User Interface when this view is executed. Please note – all views that are present by default in CRM will already contain the appropriate columns

Not sure why it is only pulling the default contact, the join is on the _companyId, so it will draw any contact, as shown here:

In the database using the SQL in my other post:

In CRM:

You can see the join is on the _companyId:

FROM Company
INNER JOIN Person on pers_companyId = comp_companyId

If it were the default, it would be:

FROM Company

INNER JOIN Comp_PrimaryPersonId = Pers_personId

We are trying to get the full listing of persons with emails, but we need to include the company info along with it. (We are forwarding this list to a marketing partner but we need it segmented)

Hi! I tried view you suggested, however, it seems to pull the default (main) contacts. Should it be pulling from PERSON for the view, instead of company?