hi_khan on Wed, 20 Nov 2019 07:13:18
Guys i have a problem in my Sql server sub queries.
i have two table one is invoice and other is receipt
1)i want to get those invoice_id which is not in receipt as well as their invoice.amount = receipt.paid_amount
2)Also those invoice_id which is in receipt but invoice.Amount > receipt.paid_amount
select student.Student_id as [Ac #], case when invoice.invoice_id in (select Recipt.invoice_id from Recipt) and invoice.Amount>Recipt.Paid_amount THEN (select invoice.Amount-Recipt.Paid_amount from Recipt) ELSE invoice.invoice_id not in (select Recipt.invoice_id from Recipt) END as amount,Fee_description as [description],invoice.Concession_amount as [conc.], invoice.month from student join allocate_class on student.Student_id = allocate_class.Student_id join session on allocate_class.session_id = session.session_id right outer join invoice on student.Student_id = invoice.Student_id right outer join Recipt on invoice.Student_id = Recipt.Student_id where invoice.Student_id =115 and session.session_id= 1 and student.status = 'active'