only get top 3 from the join (1 Viewer)

[qwertyjjj]

How do I only bring back the top 3 from the join in this query?
There will be multiple records in the InvoiceNotes table and many different invoices.

select invoice_no, inv_date, totamnt, projman, invoicenotes.note, invoicenotes.notedate
from invoices
left join invoicenotes on invoicenotes.invno = invoices.invoice_no

The InvoiceNotes tbale has an ID column so I can order by MAX(ID) DESC and then get top if needed

 

[Bogzla]

try:

select TOP 3 invoice_no, inv_date, totamnt, projman, invoicenotes.note, invoicenotes.notedate
from invoices
left join invoicenotes on invoicenotes.invno = invoices.invoice_no

should display the first 3 records only. you'll probably want to add an 'ORDER BY' line to the end to make sure you get the 3 you want...

hth,
Bogzla

 

[qwertyjjj]

No, sorry.
That selects the top 3 records of the whole lot.
I want every record in the Invoices table but only the top 3 records for every record in the InvoiceNotes table

 

[Bogzla]

Sorry, my bad (RTFQ...)

I think you'll need to use a subquery, try something like this:

WHERE InvoiceNotes.ID IN(SELECT TOP 3 InvoiceNotes.ID FROM InvoiceNotes WHERE invoicenotes.invno = invoices.invoice_no ORDER BY InvoiceNotes.ID DESC;)

the subquery *should* return the top 3 invoiceNotes.IDs for each invoice no..