Example 1 displays Constituent and Solicitor information as well as Campaign, Fund and Appeal using RECORDS as the main table

SELECT
RECORDS.CONSTITUENT_ID "Constituent ID",
RECORDS.FIRST_NAME "First Name",
RECORDS.LAST_NAME "Last Name",
T_3.FIRST_NAME "Assigned Solicitor First Name",
T_2.LAST_NAME "Assigned Solicitor Last Name",
T_4.DESCRIPTION "Assigned Solicitor Campaign",
T_5.DESCRIPTION "Appeal",
T_6.DESCRIPTION "Assigned Solicitor Fund"

FROM
DBO.RECORDS AS RECORDS
LEFT OUTER JOIN DBO.CONSTIT_SOLICITORS AS T_1 ON RECORDS.ID = T_1.CONSTIT_ID
LEFT OUTER JOIN DBO.RECORDS AS T_2 ON T_1.SOLICITOR_ID = T_2.ID
LEFT OUTER JOIN DBO.SEARCHNAME AS T_3 ON T_2.ID = T_3.RECORDS_ID
LEFT OUTER JOIN DBO.CAMPAIGN AS T_4 ON T_1.CAMPAIGN_ID = T_4.ID
LEFT OUTER JOIN DBO.APPEAL AS T_5 ON T_1.APPEAL_ID = T_5.ID
LEFT OUTER JOIN DBO.FUND AS T_6 ON T_1.FUND_ID = T_6.ID

WHERE
((RECORDS.IS_CONSTITUENT = -1))

Example 2 displays Constituent Information and Solicitor Type using CONSTIT_SOLICITORS as the main table


SELECT REC1.FIRST_NAME as 'CONSTITUENT FIRST NAME', REC1.LAST_NAME as 'CONSTITUENT LAST NAME',  REC1.CONSTITUENT_ID,
REC2.FIRST_NAME as 'SOLICITOR FIRST NAME', REC2.LAST_NAME as 'SOLICITOR LAST NAME', TE.LONGDESCRIPTION as 'SOLICITOR TYPE'

FROM
CONSTIT_SOLICITORS CS
INNER JOIN RECORDS REC1 ON CS.CONSTIT_ID = REC1.ID
INNER JOIN RECORDS REC2 ON CS.SOLICITOR_ID = REC2.ID
INNER JOIN TABLEENTRIES TE ON CS.SOLICITOR_TYPE = TE.TABLEENTRIESID

Disclaimer: Blackbaud provides programming examples for illustration only, without warranty either expressed or implied, including, but not limited to, the implied warranties of merchantability and/or fitness for a particular purpose. This article assumes you are familiar with Structured Query Language and the tools used to create and modify SQL statements and Crystal Reports. Blackbaud Customer Support may help explain the functionality of a particular procedure, but we will not modify, or assist you with modifying, these examples to provide additional functionality.