By using store procedure get the output by using the database records

Question

1.00/5 (1 vote)

See more:

I have four tables as follows;

SQL

select   *  from course_registration;
select   *  from batch_course_registration;
select   *  from bill_file;
select   *  from receipt_file;

 select   *  from course_registration;

(records as follows);

cr_bill_no  stud_id     cr_bill_dt  cmn_minor_code    cr_Active

 1	       1         2004-04-07      MFA             A                                               
 2	       2         2004-04-07      MFA             A                                            
 3	       3         2004-04-07      MFA             A                                            
 4	       4         2004-04-07      MFA             A                                           
 5	       5         2004-04-07      MFA             A

SQL

select   *  from batch_course_registration;

(records as follows);

bcr_reg_no             cr_bill_no     bcr_batch_id
APS/B/57/53247/2011        1               B8753
PH1/B/11/49243/5/2011      2               B8752
TASCO/B/74/15427/2011      3               B8745
REO/B/103/25174/5/2011     4               B8749
CHEMCO/B/44/36334/2011     5               B8774

SQL

select   *  from bill_file;

(records as follows);

 bill_no cr_bill_no   bill_dt     bill_pend_Amt  bill_active

443	  1          2011-04-22      50            A
445	  2          2011-04-29      190           A
446	  3          2011-05-02      300           A
447	  4          2011-05-09      450           A
448	  5          2011-05-09      500           A

SQL

select   *  from receipt_file;

(records as follows);

rcpt_no bill_no rcpt_dt Amt

512 443 2011-04-22 450
513 445 2011-04-29 600
514 446 2011-05-02 890
516 447 2011-05-09 950
516 448 2011-05-09 1050

From using the four tables and using store procedure i want to get the below output as follows,

how can i below output using store procedure.

stud_id bcr_batch_id cr_bill_no bill_no bill_dt bill_pend_Amt  bill_active rcpt_no rcpt_dt Amt  
   
1       B8753         1         443    2011-04-22   50            A         512   2011-04-22 450

[edit]Code block added - OriginalGriff[/edit]

Posted 10-Apr-13 1:35am

Member 8054539

Updated 10-Apr-13 1:46am

OriginalGriff

v5

Add a Solution

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)

PrashantSonewane · Answer 1 · 2013-04-10T01:57:00

Solution 1

SQL

SELECT course_registration.stud_id,
batch_course_registration.bcr_batch_id, 
bill_file.cr_bill_no,
receipt_file.bill_no,
bill_file.bill_dt,
bill_file.bill_pend_Amt,
bill_file.bill_active ,
receipt_file.rcpt_no,
receipt_file.rcpt_dt, Amt 
FROM course_registration cr 
inner join batch_course_registration  bcr on cr.cr_bill_no = crb.cr_bill_no
inner join bill_file bf on bf.cr_bill_no = crb.cr_bill_no
inner join receipt_file rf on rf.bill_no = bf.bill_no
WHERE cr.stud_id = <write here student id>

By the way what was issue while you doing it?

Posted 10-Apr-13 1:57am

PrashantSonewane

Updated 10-Apr-13 2:33am

v3

Comments

[no name] 10-Apr-13 8:20am

when i execute the above query error as follows;

Line 3
Incorrect syntax near '.'.

PrashantSonewane 10-Apr-13 8:34am

camma were missing, i added there. try again. I do not compile code as i don't have tables created.

PrashantSonewane 10-Apr-13 8:35am

Also replace <write here="" student="" id=""> with actual student id like cr.stud_id = 100 or whatever value is. Hope this help

PrashantSonewane 10-Apr-13 16:49pm

Mark as Solution if this work for you.