By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
429,401 Members | 793 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 429,401 IT Pros & Developers. It's quick & easy.

Case subquery displays in mysql

P: 1
I need to display the customer or business name as "Customer" & combine the following into 1 query:

Expand|Select|Wrap|Line Numbers
  1. SELECT a.account_id, a.cust_id, a.last_activity_date, a.open_emp_id, 
  2. CONCAT(e.fname, ' ', e.lname) AS opened_by, a.avail_balance
  3.  FROM account a INNER JOIN employee e
  4.  ON a.open_emp_id = e. emp_id
  5.  WHERE NOT EXISTS (SELECT 1
  6.  FROM transaction t
  7.  WHERE t.txn_date = a.last_activity_date);
  8.  
  9. SELECT c.cust_id, c.cust_type_cd, 
  10. CASE 
  11. WHEN c.cust_type_cd = 'I'
  12.  THEN CONCAT(i.fname, ' ', i.lname)
  13.  WHEN c.cust_type_cd = 'B'
  14.  THEN b.name 
  15. END name 
  16. FROM customer c LEFT OUTER JOIN individual i
  17.  ON c.cust_id = i.cust_id
  18.  LEFT OUTER JOIN business b 
  19. ON c.cust_id = b.cust_id;
Feb 21 '13 #1
Share this Question
Share on Google+
1 Reply


Rabbit
Expert Mod 10K+
P: 12,359
Please use code tags when posting code.

Use a union or union all to combine the queries. You just need to make sure both queries have the same number of columns.
Feb 21 '13 #2

Post your reply

Sign in to post your reply or Sign up for a free account.