加入查询时出现问题 [英] problem in Joining query

问题描述

我在加入表格查询时遇到问题

i am facing problem in joining query for the tables

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表

//Student_Fee_Quotations table

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

// FeePaid_Details

//FeePaid_Details

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

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

如果有任何记录，它即将到来表格。但是第一次在表格中没有任何记录付款......这是我的问题。

如果没有记录，则current_monthdue应该等于金额和如果没有previous_monthdue的记录，则表示previous_monthdue应为零。如下表所示

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

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

推荐答案

使用左外连接为此



并取值isnull（current_monthdue，0）as current_monthdue

use left outer join for this

and take value isnull(current_monthdue,0) as current_monthdue

这篇关于加入查询时出现问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！