Results 1 to 7 of 7
  1. #1
    Join Date
    Jun 2004
    Posts
    5

    Unanswered: Help requested with subquery

    I have a table (clients) that contains client information (client_id, name, addr, etc...) and a table (client_transactions) that contains all of my clients transactions (client_transaction_id, client_id, transaction_amount, date_of_purchase, where client_transaction_id is an auto-increment field). I am wanting to SELECT all the info from the clients table and add the latest transaction to the end (ie. name, addr, etc... transaction_amount).

    I know that I can get this with multiple queries, but I would like to see if I can do it in just one using subqueries.

    example: (this example is pseudo-code so don't take it literally)

    SELECT client_id, name, addr, [other stuff here...] FROM clients

    using the result set from the above query, I could issue a query in a FOR loop to get the latest transaction amount based on client_id returned...

    SELECT transaction_amount FROM client_transactions WHERE client_id = client_id(value from previous query) ORDER BY client_transaction_id DESC LIMIT 0,1


    Do any of you know of a quick and easy way to do this in a single query?

    Thanks,

    wlallen

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    INNER JOIN

    -PatP

  3. #3
    Join Date
    Jun 2004
    Posts
    5
    I can get the following two independent queries to work separately, but I cannot figure out how to join them without any errors:

    SELECT c2.*, ct2.transaction_amount
    FROM clients c2, client_transactions ct2
    WHERE c2.client_id = ct2.client_id

    This returns all the transaction_amounts, not just the last.



    SELECT ct1.transaction_id,
    MAX(ct1.date) FROM client_transactions ct1 WHERE
    ct1.client_id = 1 GROUP BY ct1.transaction_id ORDER BY date desc LIMIT 0,1


    This returns the wanted transaction_id and the date it occurred. Note that I used client_id = 1 so that I could simulate a single value being selected.


    Trying to put the two together is only causing errors:


    SELECT c2.*, ct2.transaction_amount
    FROM clients c2, client_transactions ct2
    WHERE c2.client_id = ct2.client_id
    AND ct2.transaction_id IN (SELECT ct1.transaction_id,
    MAX(ct1.date) FROM client_transactions ct1 WHERE
    ct1.client_id = c2.client_id GROUP BY ct1.transaction_id ORDER BY date desc LIMIT 0,1)


    Anyone have a clue as to what I am doing wrong?

    Here is my create statement to build the tables:

    # Host: localhost
    # Database: test
    # Table: 'client_transactions'
    #
    CREATE TABLE `client_transactions` (
    `transaction_id` int(11) NOT NULL auto_increment,
    `client_id` int(11) NOT NULL default '0',
    `date` datetime NOT NULL default '0000-00-00 00:00:00',
    `transaction_amount` int(11) NOT NULL default '0',
    PRIMARY KEY (`transaction_id`)
    ) TYPE=MyISAM;

    # Host: localhost
    # Database: test
    # Table: 'clients'
    #
    CREATE TABLE `clients` (
    `client_id` int(11) NOT NULL auto_increment,
    `contact_name` varchar(100) default '',
    `address` varchar(100) default '',
    `company_name` varchar(100) default '',
    `phone_number` varchar(100) default '',
    PRIMARY KEY (`client_id`)
    ) TYPE=MyISAM;

  4. #4
    Join Date
    Jun 2004
    Posts
    5
    Quote Originally Posted by Pat Phelan
    INNER JOIN

    -PatP
    Yes, but the catch is I only want returned a recordSet that contains the latest/last transaction_amount. Therefore only one record for each client, not all of the records for each and every transaction_amount. I can't figure out the last part, just getting the last transaction_amount from the client_transactions table for each client in the clients table.


    wlallen

  5. #5
    Join Date
    Jun 2004
    Posts
    5
    Considering the overwhelming response to my question I will post what I had to do to get this to work in the event someone else comes across this schenario and has no other way to do it as well:

    I had to resort to two queries, one to get a list of client_ids and another in a FOR loop to get all the client data and the last transaction_amount from the client_transactions table.

    SELECT client_id FROM cs_clients

    Once I have all the client_ids, the remaining information is obtained in a FOR LOOP:

    SELECT tbl1.*, tbl2.transaction_amount
    FROM clients tbl1
    LEFT JOIN client_transactions tbl2
    ON tbl1.client_id = tbl2.client_id
    WHERE tbl1.client_id = $CLIENT_ID_VALUE_FROM_FIRST_QUERY
    ORDER BY tbl2.date desc LIMIT 0,1

    To make it easier to assemble the results into one resultset I actually INSERT the results of the FOR LOOP into a temporary table and then I query the temp table to get all the results in one call. So instead of the desired one query to get all, I have 3 (one of which is in a FOR LOOP), which is not what I originally wanted to do.

    If anyone figures out how all this can be done in a single query to have a single resultset returned I would appreciate a reply post showing me how.

    Note that I am using MYSQL 4.0.20 where subqueries are not supported.

    Thanks Pat for taking the time to respond.


    W. Allen

  6. #6
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I'm sorry, I've been "out of the loop" for a bit. I'd try using:
    Code:
    SELECT
       FROM client_transactions AS a
       LEFT OUTER JOIN client_transactions AS b
          ON (b.client_id = a.client_id
          AND b.date > a.date)
       INNER JOIN client AS c
          ON (c.client_id = a.client_id)
       WHERE  b.date IS NULL
    Does that do what you wanted?

    -PatP

  7. #7
    Join Date
    Jun 2004
    Posts
    5
    Quote Originally Posted by Pat Phelan
    I'm sorry, I've been "out of the loop" for a bit. I'd try using:
    Code:
    SELECT
       FROM client_transactions AS a
       LEFT OUTER JOIN client_transactions AS b
          ON (b.client_id = a.client_id
          AND b.date > a.date)
       INNER JOIN clients AS c
          ON (c.client_id = a.client_id)
       WHERE  b.date IS NULL
    Does that do what you wanted?

    -PatP
    Yes that appears to work! Thanks. How do all those JOINS do that? And how does WHERE b.date IS NULL figure into the mix?

    wlallen
    Last edited by wlallen; 06-30-04 at 14:51.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •