March 10, 2007 at 7:37 am
Hello everyone
I have 2 tables as follows
Invoicedata InvoicePayment
[Invoice No] [Invoice No]
[Customer Id] [Amount Paid]
I want to get all the records from Invoice data based on Customer Id and if the same [Invoice no] exists in InvoicePayment then it should return [Amount Paid] otherwise 0.0.
I know its not very complicated but, I just cant think straight today.
Any help would be nice
Mitesh
March 10, 2007 at 11:22 am
Somthing like
SELECT
[InvoiceData].* ,
CASE WHEN [InvoicePayment].[Amount Paid] IS NULL THEN 0.0 ELSE [InvoicePayment].[Amount Paid] END AS [Amount Paid]
FROM [InvoiceData] LEFT JOIN [InvoicePayment] ON [InvoiceData].[Invoice no] = [InvoicePayment].[Invoice No]
Should do the trick.
(I dont have a sql server here to check I have got the syntax totally correct but it should be close enough)
- James
--
James Moore
Red Gate Software Ltd
March 12, 2007 at 4:13 am
Thanks Jame for your response,
I managed to get the query working slightly in different way but still using left joins.
Here is my soloution.
select
id.[Invoice No]
id.[Customer Id]
,coalesce(ip.[Amount Paid], 0.0)
from
InvoiceData id
left join
InvoicePayment ip on ip.[Invoice No] = id.[Invoice No]
Mitesh
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply