SELECT ns_ce_id AS ce_id, COALESCE(preferred, standardized, admitted, identifier, 'ERROR') AS ce_name
FROM
  ( SELECT COALESCE(psa.ns_ce_id,i.ns_ce_id) AS ns_ce_id, preferred, standardized, admitted, identifier
    FROM
      ( SELECT COALESCE(ps.ns_ce_id,a.ns_ce_id) AS ns_ce_id, preferred, standardized, admitted
        FROM
          ( SELECT COALESCE(p.ns_ce_id,s.ns_ce_id) AS ns_ce_id, preferred, standardized
            FROM
              ( SELECT ns_ce_id, TRIM(BOTH ' \r\n' FROM MAX(ns_name)) AS preferred
                FROM   ns
                WHERE  ns_parenttype = 'LS'
                AND    ns_lang = 'english'
                AND    ns_namestatus = 'preferredName'
                AND    TRIM(ns_name) != ''
                AND    ns_ce_id = 1394
                GROUP BY ns_ce_id
              ) AS p
              FULL OUTER JOIN
              ( SELECT ns_ce_id, TRIM(BOTH ' \r\n' FROM MAX(ns_name)) AS standardized
                FROM   ns
                WHERE  ns_parenttype = 'LS'
                AND    ns_lang = 'english'
                AND    ns_namestatus = 'standardizedName'
                AND    TRIM(ns_name) != ''
                AND    ns_ce_id = 1394
                GROUP BY ns_ce_id
              ) AS s
              ON p.ns_ce_id = s.ns_ce_id
          ) AS ps
          FULL OUTER JOIN
          ( SELECT ns_ce_id, TRIM(BOTH ' \r\n' FROM MAX(ns_name)) AS admitted
            FROM   ns
            WHERE  ns_parenttype = 'LS'
            AND    ns_lang = 'english'
            AND    ns_namestatus = 'admittedName'
            AND    TRIM(ns_name) != ''
            AND    ns_ce_id = 1394
            GROUP BY ns_ce_id
          ) AS a
          ON (ps.ns_ce_id = a.ns_ce_id)
      ) AS psa
    FULL OUTER JOIN
      ( SELECT ce_id AS ns_ce_id, ce_identifier AS identifier
        FROM   ce
        WHERE  ce_id = 1394
      ) AS i
    ON psa.ns_ce_id = i.ns_ce_id
  ) as n
;