Page 1 of 1

Distinct from custom table

PostPosted: Wed Jul 08, 2015 9:30 pm
by 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.

Re: Distinct from custom table  Topic is solved

PostPosted: Wed Jul 15, 2015 7:17 pm
by Mike.Sheen
2can2 wrote: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

Re: Distinct from custom table

PostPosted: Thu Jul 23, 2015 11:08 am
by 2can2
Thanks Mike. Always a simple solution.
Cheers