Fill Grid from 2 datatable or adding column on runtime

Question

i have 3 tables enquiryDetail CourseMast and FeeDetail

i have to show the information of student with coursefee and remaining and deposite fee

i m using the query

select Name,MobileNo,CourseName,CourseFees
from [AsahEnquiry].[dbo].EnquiryDetail e,[AsahEnquiry].[dbo].CourseMast c
where e.CourseId=c.CourseId AND Admitted='Yes'

which gives result

Name    MobileNo    CourseName       CourseFees
a   2222222222  Sinor Programer          30000
as  3333333555  Junier Programer    15000
as  8888888888  Junier Programer    15000
a   1221111111  Junier Programer    15000
i   4444444444  Junier Programer    15000
ku  8888777777  Sinor Programer         30000
er  6667777777  Sinor Programer         30000
a   5555555555  Junier Programer    15000
yuy 7888888888  Junier Programer    15000

i have another query

select sum(FeeDeposite) as FeeDeposite,
       (CourseFees-sum(FeeDeposite)) as FeeRemaining
from [AsahEnquiry].[dbo].Feedetail f
join [AsahEnquiry].[dbo].EnquiryDetail e on f.EnquiryID=e.EnquiryID
join [AsahEnquiry].[dbo].CourseMast c on e.Courseid=c.CourseID
group by f.EnquiryId,c.CourseFees,e.Name

which gives result

FeeDeposite FeeRemaining
16000            14000
20000            10000

i want to display it in one grid.. can anyone suggest me the single query for it... or can i add the other query result at runtime???

Answer 1

Rather than trying to turn them into a single query (you won't be able to because of the aggregation in the second query), create two views. Then you can create the third view to join the two views together. Simplify the query rather than trying to create one mega query that will be difficult to maintain in the future.

Also merging the data during page rendering is a bad idea, get the SQL server to do the work for you.

p.s. I think CourseID is the key to join the two views so make sure your aggregated view (the second query) has this field in the SELECT. Also use a LEFT JOIN otherwise you may lose records.

edit: Thanks for formatting the data, makes a lot more sense now. These are two different queries that should be kept separate. The first query shows you people who have registered for courses and how much they have paid. The second query shows you expected amount profit from each course and how much is remaining.

What are you trying to achieve?