Facing Problem in Joining two tables

Question

0.00/5 (No votes)

See more:

SQL-Server-2005

Actually i am facing problem in joining of two tables and getting the right information for example i am having two tables(Fee_Quotation,Paid_Details)

table 1
//Fee_Quotation table(while admission student will commit for this amount.here below there is one student fee_quotation details 1001)

VB

Student_ID|Fee_Type         |Fee_Amount|Payment_Type   |Commited_on
1001      |Registration_fee |100       |Annualy        |2013-mar-8
1001      |Tution_fee       |1000      |Monthly        |2013-mar-8
1001      |Books            |1000      |Annualy        |2013-mar-8

table 2
//Paid_details table(here student will pay fee according to month)

VB

Student_ID|Fee_Type         |Fee_Amount|Payment_Type |Month  |Paid_on
1001      |Registration_fee |100       |Annualy      |mar    |2013-mar-11
1001      |Tution_fee       |500       |Monthly      |mar    |2013-mar-12
1001      |Tution_fee       |500       |Monthly      |mar    |2013-mar-13
1001      |Books            |500       |Annualy      |mar    |2013-mar-11
1001      |Books            |300       |Annualy      |mar    |2013-mar-12
1001      |Tution_fee       |500       |Monthly      |apr    |2013-apr-12
1001      |Tution_fee       |400       |Monthly      |apr    |2013-apr-13

now i will pass three parameters like Student_ID=@Student_ID,Payment_type=@Payment_Type,Month=@Month
ex:- Student_ID=1001,Payment_Type='Monthly',Month='apr'
then i want the table like below

VB

Student_ID|Fee_Type   |Fee_Amount|LastMoDue|CurrentMoDue|TotalDue
1001      |Tution_Fee |1000      |0.00     |100         |100

if i will pass two parameters like
Student_ID=@Student_ID,Payment_type=@Payment_Type
ex:- Student_ID=1001,Payment_Type='Annualy'
then i want the table like below

VB

Student_ID|Fee_Type   |Fee_Amount|LastMoDue|CurrentMoDue|TotalDue
1001      |Regist_Fee |100       |0.00     |100         |100
1001      |Books      |1000      |0.00     |1000        |1000

//this type of table we can get ha sir....i mean it is possible ha sir

[MOVED] from comment [/MOVED]

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'

Posted 6-Mar-13 21:08pm

ntitish

Updated 7-Mar-13 3:39am

Maciej Los

v5

Add a Solution

Comments

Ankur^\m/ 7-Mar-13 3:23am

Your db design doesn't look good to me. 1) There is no primary key. 2) There is a lot of redundancy (tables aren't normalized).
Anyways, can you show us what query you tried and we will be able to help with what was wrong.

ntitish 7-Mar-13 3:42am

even i said to my Tl he is saying nothing will happen in our project we will not deal with more records...if we came to question i written query like below here i am getting Student_id,fee_type,Fee_Amount,Current_Month_Due

select q.Student_ID,q.Fee_Type,q.amount,q.amount-g.paid as Current_month_due 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='Registration 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='Registration Fee'

ntitish 7-Mar-13 6:24am

//this was my current code i am getting output but here again i am getting onother problem...that was i am getting out put when there is a record in table if there is no record in the table that means he is paying first time of his fee....for jan then there will not be any records na sir..then what should i do..

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'

Maciej Los 7-Mar-13 6:33am

Improve you question, use: "Improve Question" widget.

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Maciej Los · Accepted Answer · 2013-03-06T21:45:00

Solution 1

You can achieve that with stored procedure[^] like this:

SQL

CREATE PROCEDURE GetStudentFees()
    @Student_ID INT,
    @Payment_Type NVARCHAR(30),
    @Month NVARCHAR(30)
AS
BEGIN

    IF (ISNULL(@Month,'')='')
        BEGIN
            --your code to get data for 2 passed input parameters
            SELECT Student_ID, Fee_Type, SUM(Fee_Amount) AS TotalDue
            FROM YourTable
            WHERE [Student_ID] = @Studnet_ID AND [Payment_Type] = @Payment_Type
            GROUP BY [Student_ID], [Fee_Type]
        END
    ELSE
        BEGIN
            --your code to get data for 3 passed input parameters
            SELECT Student_ID, Fee_Type, SUM(Fee_Amount) AS TotalDue
            FROM YourTable
            WHERE [Student_ID] = @Studnet_ID AND [Payment_Type] = @Payment_Type AND [Month] = @Month
            GROUP BY [Student_ID], [Fee_Type]
        END
END

I don't know what you mean LastMoDue And CurrentMoDue, so... i can't help you.

Posted 6-Mar-13 21:45pm

Maciej Los

Updated 7-Mar-13 0:32am

v2

Comments

ntitish 7-Mar-13 4:37am

sir with my query i am getting only student_id,fee_type,fee_amount,current_monthdue
but i want to get last_monthdue,total_due.after getting that i can use ur syntax ...but how to get that two details

ntitish 7-Mar-13 5:16am

this was my code sir, you can see once again which i modified after that then also it is not working sir

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'

ntitish 7-Mar-13 5:38am

sir i am waiting for ur answer...

Maciej Los 7-Mar-13 6:33am

See my updated solution.

ntitish 7-Mar-13 7:30am

sir when i am paying any student fee means first i will select annualy or monthly from drop_down1 if i select annualy means according to annualy the fesses will come in a grid view if when i select monthly means another drop_down2 will display with 12 months from that we will select month according to that month monthly fesses will display of the particular student in that gridviewfee details of monthly and month of feb means i will display student_id,fee_type,amount(what they will commit while admission),previous_month_due(under this the due of the jan if any due is there means),current_month_due(under this the due of the feb if any due is there means).i think so u can understand now....if not it was my fault sir.

Maciej Los 7-Mar-13 12:16pm

Sorry, but i don't understand... ;(
You need to change sql query text depending on your needs.