Raiser’s Edge Query for Alumni Site

I am making great progress on our self-made alumni web site. Having dropped NetCommunity a few weeks ago, this solution will provide most of the functionality of NetCommunity while we build a more ambitious long-term vision for the site. This project well predates my arrival to the school, so I think that the alumni and the development office will be pleased to have a site up and running shortly.

catlin alumni menu
New items at the bottom

I spent the first few hours refining a test query to pull most of the necessary information for one record in Raiser’s Edge. I still have to add a few items (ensure alum is not deceased, for instance), but here is the query so far. You may find this useful if you are considering writing your own queries against Raiser’s Edge. The optional RODBA (read-only database assistance) module was very useful in helping me piece this together.

SELECT
RECORDS.ID, RECORDS.FIRST_NAME, RECORDS.LAST_NAME, RECORDS.MAIDEN_NAME, RECORDS.MIDDLE_NAME, RECORDS.FULL_NAME,
ADDRESS.ID as ADDRESSID, ADDRESS.ADDRESS_BLOCK, ADDRESS.CITY, ADDRESS.STATE, ADDRESS.POST_CODE, ADDRESS.DATE_LAST_CHANGED,
PHONES.PHONESID, PHONES.NUM,
TABLEENTRIES_phones.LONGDESCRIPTION AS PHONETYPE,
EDUCATION.ID AS EDUCATIONID, EDUCATION.CLASS_OF,
TABLEENTRIES_school.LONGDESCRIPTION AS SCHOOL,
CONSTIT_RELATIONSHIPS.RELATION_ID AS RELATIONID,
CONSTIT_RELATIONSHIPS.IS_EMPLOYEE,
CONSTIT_RELATIONSHIPS.IS_SPOUSE,
CONSTIT_RELATIONSHIPS.DATE_FROM,
CONSTIT_RELATIONSHIPS.DATE_TO,
CONSTIT_RELATIONSHIPS.POSITION,
RECORDS_relations.FIRST_NAME AS RELATION_FIRST_NAME,
RECORDS_relations.LAST_NAME AS RELATION_LAST_NAME,
RECORDS_relations.ORG_NAME AS RELATION_ORG_NAME,
TABLEENTRIES_industry.LONGDESCRIPTION AS INDUSTRY,
TABLEENTRIES_profession.LONGDESCRIPTION AS PROFESSION
FROM
RECORDS,
CONSTIT_ADDRESS,
CONSTIT_ADDRESS_PHONES,
ADDRESS,
PHONES,
TABLEENTRIES AS TABLEENTRIES_phones,
EDUCATION,
TABLEENTRIES AS TABLEENTRIES_school,
CONSTIT_RELATIONSHIPS,
RECORDS AS RECORDS_relations,
TABLEENTRIES AS TABLEENTRIES_industry,
TABLEENTRIES AS TABLEENTRIES_profession
WHERE
RECORDS.LAST_NAME = 'Kassissieh' AND RECORDS.FIRST_NAME = 'Richard' AND
RECORDS.ID = CONSTIT_ADDRESS.CONSTIT_ID AND
CONSTIT_ADDRESS.ADDRESS_ID = ADDRESS.ID AND CONSTIT_ADDRESS.PREFERRED = '-1' AND
CONSTIT_ADDRESS_PHONES.CONSTITADDRESSID = CONSTIT_ADDRESS.ID AND
CONSTIT_ADDRESS_PHONES.PHONESID = PHONES.PHONESID AND
PHONES.PHONETYPEID = TABLEENTRIES_phones.TABLEENTRIESID AND
PHONES.DO_NOT_CALL = '0' AND
EDUCATION.RECORD_ID = RECORDS.ID AND
EDUCATION.SCHOOL_ID = TABLEENTRIES_school.TABLEENTRIESID AND
RECORDS.ID = CONSTIT_RELATIONSHIPS.CONSTIT_ID AND
CONSTIT_RELATIONSHIPS.RELATION_ID = RECORDS_relations.ID AND
CONSTIT_RELATIONSHIPS.INDUSTRY = TABLEENTRIES_industry.TABLEENTRIESID AND
CONSTIT_RELATIONSHIPS.PROFESSION = TABLEENTRIES_profession.TABLEENTRIESID";

2 comments

  1. Peter Gulka says:

    My big reservation for going right to the SQL is that the table structure is beyond insanity.

    Are you trusting RODBA to give you the right SQL code? Are you editing what it gives you?

    Pete
    http://reusers.server-plane

  2. rkassissieh says:

    I hope my sample query (above) is helpful to you. I use RODBA to help me make sense of the RE tables and then write my own query.