CODEWITHSUNDEEP
c#sqldatabase
arin
arin

Reputation: 600

Getting data from database from different tables

I'm creating this mobile portal/application using (ASP + WML) were I need to generate a list of courses taken by a student in certain semester in a certain year.

I have the following tables:

enter image description here

I have from a previous page the following parameters:

string StudentId = Request.Params["StudentId"];
string Year = Request.Params["Year"];
string Semester = Request.Params["Semester"];

As a sample of the data inside the tables.

enter image description here

I'm stuck here

string connectionString = @"Data Source=.\SQLEXPRESS;AttachDbFileName=|DataDirectory|\Uni.MDF;" +
            "Integrated Security=True;User Instance=True";
           string queryString =" ??????????????????????????? " ;

I want to get a page to show the courses that the user is registered in (in a given semester). The information will be later passed in a URL to new page where the info will be shown.

Something like:

First Semester  2010
Student : Arin Rizk

Course Name     No. of Credit      Mark
    AAA                3            65
    BBB                3            23
    CCC                3            65
    DDD                3            58
    EEE                3            70

Your GPA for this semster is 3.12

Edit

how I will print the the info in the page later ?

How can I do it? I tried to create a view but I don't know why its not working.

I can't change the tables or the DB structure.

Thank you in advance.

Upvotes: 3

Views: 1046

Answers (2)

Greg
Greg

Reputation: 8784

For the course list you can do:

Select Courses.CourseName, Courses.NumberOfCredits, RegisteredIn.Mark
From dbo.Courses Inner Join dbo.RegisteredIn On Courses.id = RegisteredIn.CourseID
    Inner Join dbo.Semester On RegisteredIn.SemesterID = Semester.id
Where RegisteredIn.StudentID = '20111' AND
    Semester.Title = 'First' AND 
    Semester.Year = 2011
Order By Courses.CourseName

Upvotes: 0

Baz1nga
Baz1nga

Reputation: 15579

select st.firstname + ' ' + st.lastname,se.year, c.coursename,c.credits,ri.mark 
from students st 
inner join RegisteredIn ri on ri.studentid=st.id
inner join semester se on se.id=ri.semesterid
inner join Courses c on c.id=se.courseid

this query will give you the name, year, coursename, credits and marks.

You need to write another query for gpa calculation or you could calculate on your web server with the data obtained above.

Upvotes: 3

Related Questions