I realize that mySQL does not support subqueries (at this time).
I have 2 tables: contacts and transactions
Here is the structure:
CREATE TABLE contacts (
contact_id int(6) NOT NULL auto_increment,
first_name varchar(50) default NULL,
last_name varchar(50) default NULL,
email varchar(50) default NULL,
)
CREATE TABLE transactions (
transaction_id int(6) NOT NULL auto_increment,
date_time datetime default NULL,
buyer_id int(6) default NULL,
start_issue int(4) default NULL,
term int(2) default NULL,
price decimal(6,2) default NULL,
date_paid date default NULL,
)
They relate to each other this way:
contacts.contact_id = transactions.buyer_id
This is a one to many relation (1 contact can have several transactions)
The date_paid field has a date if they have paid and is NULL if they have not.
I want to pull out data from the contacts table where the last tranasaction related to that contact is unpaid.
Here's what I have so far:
$result = mysql_query ("SELECT contacts.contact_id, contacts.first_name, contacts.last_name, MAX(transactions.transaction_id) FROM contacts, transactions WHERE contacts.contact_id = transactions.buyer_id AND transactions.date_paid IS NULL GROUP BY contacts.contact_id ORDER BY contacts.contact_id");
This is not working as expected. It is pulling out any record that is unpaid regardless of whether or not it is the last record. Shouldn't the MAX() function ensure that it is the last transaction?
The long (and unoptimized) way to do this is to pull all of the MAX() transactions (regardless of date_paid) and then loop thru it to see if they are paid or not and put the unpaid ones into an array. This seems pretty ineffcient. Is there a better way?
thanks for listening.
