#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2013
    Posts
    13
    Rep Power
    0

    Case subquery displays


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

    SELECT a.account_id, a.cust_id, a.last_activity_date, a.open_emp_id,
    CONCAT(e.fname, ' ', e.lname) AS opened_by, a.avail_balance
    FROM account a INNER JOIN employee e
    ON a.open_emp_id = e. emp_id
    WHERE NOT EXISTS (SELECT 1
    FROM transaction t
    WHERE t.txn_date = a.last_activity_date);

    SELECT c.cust_id, c.cust_type_cd,
    CASE
    WHEN c.cust_type_cd = 'I'
    THEN CONCAT(i.fname, ' ', i.lname)
    WHEN c.cust_type_cd = 'B'
    THEN b.name
    END name
    FROM customer c LEFT OUTER JOIN individual i
    ON c.cust_id = i.cust_id
    LEFT OUTER JOIN business b
    ON c.cust_id = b.cust_id;
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,235
    Rep Power
    4279
    how did you want them combined? in a UNION?

    or are account employees somehow related to customer individual businesses?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

IMN logo majestic logo threadwatch logo seochat tools logo