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

INNER JOIN

-PatP

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;

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

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

I'm sorry, I've been "out of the loop" for a bit. I'd try using: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