Distinct from custom table

[2can2]

Hi,
I have added some code to the end of usp_Jiwa_invoices_invoice in V70115 and want to Select the TOP 1 record From DB_Notes for a Note Type = 'hard coded id'. I am battling to get only One record of a Specific Note Type (say GenPackNote) where there could be more than 1.
Code as below - can you show me the correct syntax. I think I need the Top1 in a separate Select ???
SELECT #TempTable2.*, DB_Main.Phone AS DrPhone, SO_Lines.Discount AS Discount,
tbLineDBNotes_GenPack.NoteText as GenPackNotes
FROM #TempTable2
INNER JOIN SO_Lines ON #TempTable2.InvoiceLineID = SO_Lines.InvoiceLineID
INNER JOIN DB_Main ON #TempTable2.AccountNo = DB_Main.AccountNo
--- HAVE to do this in case no 'GenPackerNotes' records and then can pass NULL!!
LEFT OUTER JOIN
(SELECT TOP 1 DB_Notes.DebtorID, DB_Notes.NoteText
FROM DB_Notes
WHERE (DB_Notes.NoteTypeID = '20bd5f02-810d-4c6f-9322-15ae6ab54090')) AS tbLineDBNotes_GenPack
ON DB_Main.DebtorID = tbLineDBNotes_GenPack.DebtorID
Thanks.

[Mike.Sheen]

want to Select the TOP 1 record From DB_Notes for a Note Type = 'hard coded id'. I am battling to get only One record of a Specific Note Type (say GenPackNote) where there could be more than 1.

A subquery is probably what you are looking for:

Code: Select all

SELECT SO_Main.InvoiceNo, DB_Main.Name, (SELECT TOP 1 DB_Notes.NoteText FROM DB_Notes WHERE DB_Notes.DebtorID = DB_Main.DebtorID AND DB_Notes.NoteTypeID = '32F2495E-FA0D-49D0-8A49-40615BFB53D7')
FROM SO_Main
JOIN DB_Main ON DB_Main.DebtorID = SO_Main.DebtorID

Mike

[2can2]

Thanks Mike. Always a simple solution.
Cheers