problem in Joining query

Question

0.00/5 (No votes)

See more:

SQL-Server-2005

i am facing problem in joining query for the tables

SQL

select
   q.Student_ID
  ,q.Fee_Type
  ,q.amount
  ,q.amount-p.paid as Current_month_due
  ,q.amount-g.paid as previous_month_due
  ,(q.amount-g.paid)+(q.amount-p.paid) as total_amount
from
  Student_Fee_Quotations q
  join
  (
     select
        d.Student_ID
       ,d.Fee_Type
       ,sum(d.amount) as paid
     from
       FeePaid_Details d
     where
       d.Student_ID='PS20130001'
       and d.Fee_Type='Admission Fee'
       and d.Month_Details='jan'
     group by
        d.Student_ID
       ,d.Fee_Type
  ) as g
  on q.Student_ID='PS20130001' and q.Fee_Type='Admission Fee'
  join
  (
     select
        d.Student_ID
       ,d.Fee_Type
       ,sum(d.amount) as paid
     from FeePaid_Details d
     where
       d.Student_ID='PS20130001'
       and d.Fee_Type='Admission Fee'
       and d.Month_Details='feb'
     group by
        d.Student_ID
       ,d.Fee_Type
  ) as p
  on q.Student_ID='PS20130001' and q.Fee_Type='Admission Fee'

//Student_Fee_Quotations table

VB

student_id|fee_type   |amount|
1001      |tution_fee |1000  |

//FeePaid_Details

VB

student_id|fee_type   |month|paid_amount
1001      |tution_fee |jan  |500
1001      |tution_fee |Feb  |300

the above code for getting the below table

VB

student_id|fee_type  |amount|previous_monthdue|current_monthdue|total_due
1001      |tution_fee|1000  |500              |700             |1200

it is coming if there is any records in the table.but for the first time there will not be any records in the table for payment...that was my problem.
if there is no record means for the current_monthdue should be equal to amount and if there is no records of previous_monthdue means previous_monthdue should be zero.like below table

VB

student_id|fee_type  |amount|previous_monthdue|current_monthdue|total_due
1001      |tution_fee|1000  |  0              |1000            |1000

Posted 7-Mar-13 1:10am

ntitish

Updated 7-Mar-13 2:15am

v6

Add a Solution

Comments

Davidduraisamy 7-Mar-13 7:15am

What issue you are facing?

ntitish 7-Mar-13 7:18am

above at last i updated my problem..

Davidduraisamy 7-Mar-13 7:24am

Try this:
select q.Student_ID,q.Fee_Type,q.amount,case when q.amount IS null then q.amount else q.amount-p.paid end as Current_month_due,case when g.paid is null then 0 else q.amount-g.paid end as previous_month_due,(q.amount-g.paid)+(q.amount-p.paid) as total_amount from Student_Fee_Quotations q join
(select d.Student_ID,d.Fee_Type,sum(d.amount) as paid from FeePaid_Details d where
d.Student_ID='PS20130001' and d.Fee_Type='Admission Fee' and d.Month_Details='jan'
group by d.Student_ID,d.Fee_Type )as g on q.Student_ID='PS20130001' and q.Fee_Type='Admission Fee' join (select d.Student_ID,d.Fee_Type,sum(d.amount) as paid from FeePaid_Details d where
d.Student_ID='PS20130001' and d.Fee_Type='Admission Fee' and d.Month_Details='feb'
group by d.Student_ID,d.Fee_Type ) as p on q.Student_ID='PS20130001' and q.Fee_Type='Admission Fee'

ntitish 7-Mar-13 7:48am

sir i think so you are not getting my point. from above in second table(FeePaid_Details) if there is no records then my table should be like 4th table (at last i updated na) sir that was my problem i am joining p,g to q if there is no records in p or if there is no records in g or there is no records in p,g the table should be like fourth table...

1 solution

Add a Solution

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

itsmehaboob · Answer 1 · 2013-03-07T01:20:00

Solution 1

use left outer join for this

and take value isnull(current_monthdue,0) as current_monthdue

Posted 7-Mar-13 1:20am

itsmehaboob

Comments

ntitish 7-Mar-13 7:33am

above is my code can u modify that if you dont mind means ...dont think badly sir i am confused from morning with this query i am not getting the exact point...if u can only sir other wise thanks for your valuable help....

itsmehaboob 7-Mar-13 7:42am

Please try this.

SELECT q.student_id, q.fee_type, q.amount, q.amount - p.paid AS Current_month_due, ISNULL(q.amount, 0) - ISNULL(g.paid, 0) AS previous_month_due, (q.amount - g.paid)
+ (q.amount - p.paid) AS total_amount
FROM Student_Fee_Quotations AS q LEFT OUTER JOIN
(SELECT student_id, fee_type, SUM(paid_amount) AS paid
FROM FeePaid_Details AS d
WHERE (student_id = '1') AND (fee_type = 'Admission Fee') AND (month = 'jan')
GROUP BY student_id, fee_type) AS g ON q.student_id = '1' AND q.fee_type = 'Admission Fee' LEFT OUTER JOIN
(SELECT student_id, fee_type, SUM(paid_amount) AS paid
FROM FeePaid_Details AS d
WHERE (student_id = '1') AND (fee_type = 'Admission Fee') AND (month = 'feb')
GROUP BY student_id, fee_type) AS p ON q.student_id = '1' AND q.fee_type = 'Admission Fee'

ntitish 7-Mar-13 8:08am

sir my problem you are not getting it was my fault only sir. i am unable to explain u clearly...sir again i will explain you if you are interested.sir we are joining tables na sir down i am explaining clearly

q AS Student_Fee_Quotations

g AS(SELECT student_id, fee_type, SUM(paid_amount) AS paid FROM FeePaid_Details AS d WHERE (student_id = '1') AND (fee_type = 'Admission Fee') AND (month = 'jan') GROUP BY student_id, fee_type)

p AS (SELECT student_id, fee_type, SUM(paid_amount) AS paid FROM FeePaid_Details AS d WHERE (student_id = '1') AND (fee_type = 'Admission Fee') AND (month = 'feb') GROUP BY student_id, fee_type)

like the above we are taking na sir if there is no records in g or if there is no records in p or if there is no records in p or g then the table should display like forth table in my question.sir i think u got my point.but with ur query it is not getting...is there any solution for this sir..

problem in Joining query

1 solution

Solution 1

Add your solution here

Preview 0

problem in Joining query

1 solution

Solution 1

Add your solution here

Preview 0

Existing Members

...or Join us