MySql Select query (limited selected on JOIN)

I have a 2 tables: tbl_customers, and tbl_customers_notes

I'm displaying all my customers in table rows (as one does), and I want to add a column that is for displaying the last known datetime record in the related tbl_customers_notes table.

Obviously this is a one-to-many relationship where the matching recID is going to be customerid.

Here is what I have so far:

<?php
$result = mysql_query("SELECT `customername` FROM `tbl_customers` ");
while($row = mysql_fetch_array( $result )) {
?>  
    <tr>
      <td><?php echo $customername;开发者_运维知识库?></td>
      <td><?php echo 'note datetime'; ?></td>
    </tr>
<? } ?> 

If I do a JOIN with the notes table I get duplicates. Can I limit the tbl_customers_notes to just select the last known record for that customerid?

Try this:

select c.customerid, c.customername, max(cn.note_date)
from tbl_customers c
left join tbl_customers_notes cn on c.customerid = cn.customerid
group by  c.customerid, c.customername

The reason for grouping by both customerid and customername is that in case same customername could have different customerids. Outer join is to include customers that have no notes.

SELECT `customername`, max_datetime FROM `tbl_customers` c left join 
    (select max(datetime) max_datetime, customerid 
    from tbl_customers_notes cn group by customerid) cn on 
 on c.id=cn.customerid;

I think a HAVING is in order.

SELECT c.customername, n.note_date
FROM tbl_customers c
         INNER JOIN tbl_customers_notes n ON c.customerid = n.customerid
GROUP BY c.customername
HAVING n.note_date = MAX(n.note_date)