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) ;