%%(language-ref)
SELECT cand.candidate_id, cand.prefname, c.lastname, c.firstname, c.middlename, c.nickname, c.suffix, c.state_id FROM
(SELECT c.candidate_id, c.firstname prefname
FROM candidate c
JOIN candidatepreferredname cpn USING (candidatepreferredname_id)
WHERE cpn.candidatepreferredname_id = 'F'
UNION ALL
SELECT c.candidate_id, c.middlename prefname
FROM candidate c
JOIN candidatepreferredname cpn USING (candidatepreferredname_id)
WHERE cpn.candidatepreferredname_id = 'M'
UNION ALL
SELECT c.candidate_id, c.nickname prefname
FROM candidate c
JOIN candidatepreferredname cpn USING (candidatepreferredname_id)
WHERE cpn.candidatepreferredname_id = 'N') cand
LEFT JOIN candidate c USING (candidate_id) ;
%%