Getting data from database from different tables

Question

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"];

the values of the (Year) is either "2011" or "2012".
the values of (Semester) is either "First" or "Second" or "Summer".
the values of (StudentId) is a Number extracted before from id in Students table.

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.

Please don't delete all of the contents of the question to delete it. — Jamie F
– Jamie F, Commented Apr 16, 2012 at 21:54

Baz1nga · Accepted Answer · 2012-04-15 20:53:29Z

3

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.

answered Apr 15, 2012 at 20:53

Baz1nga

15.6k3 gold badges38 silver badges63 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Greg · Accepted Answer · 2012-04-15 20:56:12Z

0

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

answered Apr 15, 2012 at 20:56

Greg

8,87421 gold badges72 silver badges110 bronze badges

Collectives™ on Stack Overflow

Getting data from database from different tables

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related