To create a view (in order to make my queries more straightforward) I had to build some SQL that queried the same table twice. My successful query looks like this:
SELECT lookup_primary_character_id, PrimChar.character_name, lookup_related_character_id, RelChar.character_name, relationship
FROM CharToCharRels
JOIN Characters AS PrimChar ON lookup_primary_character_id = PrimChar.character_id
JOIN Characters AS RelChar ON lookup_related_character_id = RelChar.character_id

The trick was to do one SELECT and two JOINs to the same table, using different aliases (PrimChar and RelChar) in the JOINs.

I extended this method to produce a query that creates a result which includes the name info and the cite info, thusly:
SELECT lookup_rel_id, lookup_cite_id, CharToCharRels.id, lookup_primary_character_id, PrimChar.character_name AS primaryChar, lookup_related_character_id, RelChar.character_name AS relatedChar, relationship
FROM CharToCharRels
JOIN Characters AS PrimChar ON lookup_primary_character_id = PrimChar.character_id
JOIN Characters AS RelChar ON lookup_related_character_id = RelChar.character_id
JOIN CharToCharToCiteRels AS cites ON lookup_rel_id = CharToCharRels.id

NOTE: Martin pointed out that Stewart had solved this, and blogged about it A YEAR AGO!! Grrr.