Cacher is the code snippet organizer for pro developers

SELECT t1.transaction_no, t1.create_time, t1.amount_point, t1.user_id, t2.name,
  case
    WHEN t2.open_loop="T" THEN t3.user_id
    WHEN t2.open_loop="F" THEN t3.external_id
  END AS external_id
FROM transaction_issuer t1
  INNER JOIN merchant_scheme t2 ON t1.scheme_id=t2.scheme_id
  INNER JOIN user t3 ON t1.user_id=t3.user_id
WHERE t1.status!="pending"