So I've tried to search for this one, but it's difficult to search for, and the MySQL manual is annoying to search through anyway.

I have 2 tables, one for customers, and the other for notes. They are linked together on a customerID column. All I want to see is all the customer information and the most recent note (if any. So I do a statement like this:

SELECT * FROM customers LEFT JOIN notes ON customers.customerID = notes.noteID ORDER BY notes.date_added

The problem with this is if there are 5 notes about the same customer, it will bring back 5 rows with the same customer and 5 different notes. I only want 1, the most recent one. How do I do this using only 1 statement?

Here is an example of how to do what I want using 2 statements in PHP (BUT NOT WHAT I WANT):

$query = mysql_query("SELECT * FROM customers");
while ($row = mysql_fetch_array($query))
{
$query2 = mysql_query("SELECT * FROM notes WHERE customerID='$row[customerID]' ORDER BY date_added LIMIT 1");
$row2 = mysql_fetch_array($query2);
}


I don't like this though. There has to be a way to do it all in 1 SQL statement. If you have a database of 500 customers, then you are doing extra queries you don't need to do.

The customers and notes thing is just an example. There are lots of ways this could be used.

$sql = "SELECT c.*, n.* FROM `customers` c, `notes` n WHERE c.customerID= '".$row['customerID']."' AND c.customerID = n.customerID ORDER BY n.date_added LIMIT 1";

try that?

Actually my code is not the same as the LEFT JOIN, so you might not want to use that (it doesn't return a customer row if there's no notes row for that customer ID).

Your original code is pretty much correct except you're joining the wrong column I think.

LEFT JOIN notes ON customers.customerID = notes.noteID

this should be:
LEFT JOIN notes ON customers.customerID = notes.customerID

oops, ya, that was a typo. It shouldbe customers.customerID = notes.customerID

Your above code, however, will only return 1 entry. I want to return ALL the customers with the most recent note joined to it if it exists. So if there are 11 customers and 15 notes, I want 11 rows returned to me.

^^^ Agree with that.

Also if you want only the one most recent note you probably need:
ORDER BY notes.date_added DESC
LIMIT 1

oops, ya, that was a typo. It shouldbe customers.customerID = notes.customerID

Your above code, however, will only return 1 entry. I want to return ALL the customers with the most recent note joined to it if it exists. So if there are 11 customers and 15 notes, I want 11 rows returned to me.

You're right, I don't know what I was thinking when I wrote LIMIT = 1 in there.
Did you try adding WHERE clause to filter your result?


$sql = "SELECT * FROM customers LEFT JOIN notes ON customers.customerID = notes.noteID WHERE notes.date_added IN (
SELECT MAX( date_added ) FROM `notes` GROUP BY notes.customer_id DESC) ORDER BY notes.date_added";

I hastily put that together, looks like it would work but I don't have the database next to me to test it and it might need some modifications.

Your above code, however, will only return 1 entry. I want to return ALL the customers with the most recent note joined to it if it exists. So if there are 11 customers and 15 notes, I want 11 rows returned to me.
Hmm, that one's trickier. I think it should be possible with a group by and having clause but like sc_freak I'd probably try to do it with a subquery. Here's my attempt:
SELECT user.id as i, user.name, etc,
(SELECT notefield FROM notes
WHERE userid=i
ORDER BY added DESC
LIMIT 1
) AS note
FROM user
ORDER BY whatever;

ccole this should fit your needs, it is tested in a small table.


$sql = "SELECT * FROM customers LEFT JOIN notes ON customers.customerID = notes.customerID
WHERE notes.date_added IN (
SELECT MAX( date_added )
FROM `notes`
GROUP BY notes.customerID DESC
)
OR notes.date_added IS NULL
ORDER BY notes.date_added";


Hope it helps,

Van

Try:

SELECT *
FROM (
SELECT `customers`.`name`,
`customers`.`id` AS 'customerid',
`note`.`note`,
`note`.`when`
FROM `customers`
LEFT JOIN `note` ON `note`.`cust`=`customers`.`id` order by `when` DESC
) AS results
GROUP BY `customerid`

Xeentech solution is better than mine, and it should work given the right column name.