Click here to Skip to main content
16,016,962 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
SQL
select * from tb_courseelg_settings where coursename = 'MFA' or coursename = 'AFF' 

When i run the above query output as follows
Coursename Eligibility
  MFA         EFA
  AFF         FPFF

First query as follows
SQL
select distinct Course = case cr.cpm_pkg_id WHEN '' THEN cr.cmn_minor_code else  cbm.cmn_minor_code end
from course_registration cr, batch_course_registration bcr, co_batch_master cbm where bcr.cr_bill_no  = cr.cr_bill_no and cbm.cbm_batch_id = bcr.bcr_batch_id and cr.cr_active = 'A' and cr.stud_id = '9917'

When i run the above query output as follows
Course
MFA
AFF

Second query as follows
SQL
select distinct a.pm_prof_code as Code,a.sp_cert_no as Certificate_No,
a.sp_issu_authority as Issue_Authority, convert(char(14),a.sp_issu_dt,106) as Issue_Date from student_professional a,tb_courseelg_settings b
where a.pm_prof_code= b.courseelg and b.coursename ='MFA' and a.stud_id = '9917' 

When i run the above query output as follows
Code  Certificate_No   Issue_Authority  Issuedate
EFA    B174/11           HIMT          16 Jul 1999  


I want to combine the above First query and second query and output as follows

Code  Certificate_No   Issue_Authority  Issuedate
EFA    B174/11           HIMT          16 Jul 1999  
FPFF

Note: Because MFA eligbility is EFA and AFF eligbility is FPFF.

For AFF no record is there in the tb_courseelg_settings.

So i want to add blank row.

for that how can i do in asp.net using c#.

Regards,
Narasiman P.
Posted
Updated 10-Mar-15 20:52pm
v4

At the first look i see one problem: you're mixing INNER JOIN with CROSS JOIN.

SQL
from course_registration cr, batch_course_registration bcr, co_batch_master cbm

Above means: CROSS JOIN. Only for two tables you're using INNER JOIN, by specifying relationship between them:
SQL
where a.pm_prof_code= b.courseelg
but you should use LEFT JOIN instead!


For further information, please see this excelent article: Visual Representation of SQL Joins[^]
 
Share this answer
 
Comments
King Fisher 11-Mar-15 6:50am    
5+ Master.
Maciej Los 11-Mar-15 10:00am    
Thank you, My Lord ;)
Below is the query of your requirement.
SQL
select first.Code,second.Certificate_No,second.Issue_Authority,second.Issue_Date
from

(select distinct Course = case cr.cpm_pkg_id WHEN '' THEN cr.cmn_minor_code else  cbm.cmn_minor_code end
from course_registration cr, batch_course_registration bcr, co_batch_master cbm where bcr.cr_bill_no  = cr.cr_bill_no and cbm.cbm_batch_id = bcr.bcr_batch_id and cr.cr_active = 'A' and cr.stud_id = '9917') as first

Left outer join 

(select distinct a.pm_prof_code as Code,a.sp_cert_no as Certificate_No,
a.sp_issu_authority as Issue_Authority, convert(char(14),a.sp_issu_dt,106) as Issue_Date from student_professional a,tb_courseelg_settings b
where a.pm_prof_code= b.courseelg and b.coursename ='MFA' and a.stud_id = '9917' ) as second

ON(first.code = second.code)
 
Share this answer
 

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



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900