Freelancer
Freelancer

Reputation: 9074

Joins not working in described condition

I have two tables.

Table1->Payment

AdmissionNum(PK)  |  StudName |   Fees |

       1               sagar      5000 
       2               nilesh     6000
       3               amar       4000  

Table2-> paymentDetails

AdmissionNum(FK) | RemainingFees |   Date       |  Payment
        1             4000          1/1/2012          1000
        2             5000          2/5/2012          1000
        1             3500          2/10/2012         1500
        1             2500          2/15/2012         1000
        2             2500          2/20/2012         2500
        3             0             3/25/2012         4000

I have tried different queries but was not able to make joint between payment table and paymentDetails table. I am making C# windows app. When particular student is paying fees his record is stored in paymentDetails table. I want to extract only outstanding fees details i.e. student whose Remaining Fees is greater than 0 but the last details in the paymentDetais table i.e. .

My expected resut table is-

AdmissionNum  |  StudName |   Fees | RemainingFees

       1           sagar      5000    2500
       2           nilesh     6000    2500

Upvotes: 0

Views: 93

Answers (4)

Joe G Joseph
Joe G Joseph

Reputation: 24086

try this:

with cte as (select P.AdmissionNum , P.StudName,   P.Fees , D.RemainingFees,row_number() 
over ( partition by  P.AdmissionNum order by [DATE] desc) as rn
from Payment P
join PaymentDetails D
on P.AdmissionNum=D.AdmissionNum
where RemainingFees>0)
select AdmissionNum ,StudName,  Fees , RemainingFees
from cte
where rn=1


SQL fiddle demo

Upvotes: 2

Habibillah
Habibillah

Reputation: 28695

Try query bellow:

Select a.AdmissionNum, a.StudName, a.Fees, b.RemainingFees 
from Payment a 
inner join paymentDetails b on a.AdmissionNum = b.AdmissionNum
inner join (
   select AdmissionNum, max(date) as date 
   from paymentDetails 
   group by AdmissionNum
) c on a.AdmissionNum = c.AdmissionNum
where b.RemainingFees > 0 and b.Date = c.Date

Upvotes: 0

Yogendra Singh
Yogendra Singh

Reputation: 34367

Try below:

   SELECT a.AdmissionNum, a.StudName , a.Fees, b.remFees as RemainingFees
   FROM Payment a, 
        (SELECT AdmissionNum, min(RemainingFees) as remFees 
           FROM PaymentDetails b GROUP BY AdmissionNum) b
   WHERE a.AdmissionNum = b.AdmissionNum 
        and b.remFees >0;

Upvotes: 0

Vikdor
Vikdor

Reputation: 24134

I would do it as follows.

SELECT 
       P.AdmissionNum,
       P.StudName,
       P.Fees
       P.Fees - PD.Payment 'RemainingFees'
FROM
    Payment P
    JOIN (SELECT 
                AdmissionNum, 
                SUM(Payment) 'Payment' 
          FROM 
                PaymentDetails 
          GROUP BY AdmissionNum
    ) AS PD
    ON P.AdmissionNum = PD.AdmissionNum
WHERE 
    P.Fees - PD.Payment > 0

Upvotes: 0

Related Questions