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

    Join Date
    Feb 2013
    Posts
    13
    Rep Power
    0

    Display problems


    I am trying to do a query that displays the customer name with the "customer" label. Here's what I've done so far:

    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
    CONCAT (i.fname, i.lname, b.name) AS Customer
    FROM individual i INNER JOIN business b
    on b.cust_id = i.cust_id
    WHERE NOT EXISTS (SELECT 1
    FROM transaction t
    WHERE t.txn_date = a.last_activity_date);

    I got this error message:

    Error Code: 1064
    You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'CONCAT (i.fname, i.lname, b.name) AS Customer
    FROM individual i INNER JOIN busin' at line 5

    It had been working great until I added the 2nd CONCAT & INNER JOIN.

    Thanks in advance for helping someone who's still learning.
  2. #2
  3. --
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2012
    Posts
    3,957
    Rep Power
    1046
    Hi,

    I don't have the slightest idea what you're trying to do. What is this "2nd CONCAT & INNER JOIN" supposed to do?

    In any case, the second CONCAT makes no sense in this context, and you cannot have multiple "FROM" and "WHERE" for a single query.
    The 6 worst sins of security ē How to (properly) access a MySQL database with PHP

    Why canít I use certain words like "drop" as part of my Security Question answers?
    There are certain words used by hackers to try to gain access to systems and manipulate data; therefore, the following words are restricted: "select," "delete," "update," "insert," "drop" and "null".
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2013
    Posts
    11
    Rep Power
    0
    how the result table looks like?
  6. #4
  7. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,120
    Rep Power
    4274
    Originally Posted by bstar
    It had been working great until I added the 2nd CONCAT & INNER JOIN.
    there is only one SELECT clause in any query, and only one FROM clause

    you can't just patch on some new columns and a new FROM clause to an existing query

    your first two tables, a nd e, are properly joined, and your second two tables, i and b, are also properly joined, but you will need either to set up two SELECTs in a UNION, or else find some way to logically connect a/e to i/b
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

IMN logo majestic logo threadwatch logo seochat tools logo