CODEWITHSUNDEEP
phpmysqlselectleft-joingreatest-n-per-group
coffeemonitor
coffeemonitor

Reputation: 13120

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?

Upvotes: 2

Views: 238

Answers (3)

Andrej
Andrej

Reputation: 7504

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;

Upvotes: 1

Hong Ning
Hong Ning

Reputation: 1003

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.

Upvotes: 2

Matt McHugh
Matt McHugh

Reputation: 4095

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)

Upvotes: 0

Related Questions