locked
Creating a table from data in two or more tables RRS feed

  • Question

  • User-226101726 posted

    Im creating a gradebook to print reports for my school and im unable to complete two tasks using SQL.

    Task one

    I have a student table, Class table, ClassSubjects table ( This allows me to enter the subjects assigned to that class),

    a classStudent table( allows me to enter the students in a specific class), and a subjects table. I want to be able to create, add and update 

    in anew table, all the students from a specific class and all the subjects the do based on the subjects assigned to that class.

    The system should be anle to do this automatically without me entering them one by one seeing all the data is there already

    so that later I can add the grades for an exam for each subject for each student based on the class they r in.

    The second is to display the data like a gradebok and be able to add the grades in the gradebook and all the table are updated.

    Any Suggestions anyone please HELP 

    Friday, August 12, 2011 2:21 AM

All replies

  • User1553110397 posted

    Please try this

    select c.Classname,std.[Student Name],subjectname,0 as Mark ,'' asGrade into gradebok

    from

    dbo.Class c

    inner

    join

    ClassStudent cs

    on

    c

    .ClassID =cs.ClassID

    inner

    join

    dbo

    .Student std

    on

    std

    .StudentID = cs.StudentID

    inner

    join

    dbo

    .ClassSubject csub

    on

    csub

    .ClassID = cs.ClassID

    inner

    join

    dbo

    .[Subject] sub

    on

    csub

    .SubjectID = sub.SubjectID

    --where className='2 Group'

    order

    by std.[Student Name]

    select

    c.Classname,std.[Student Name],subjectname,0 as Mark ,'' as Grade

    into

    gradebok

    from

    dbo

    .Class c

    inner

    join

    ClassStudent cs

    on

    c

    .ClassID =cs.ClassID

    inner

    join

    dbo

    .Student std

    on

    std

    .StudentID = cs.StudentID

    inner

    join

    dbo

    .ClassSubject csub

    on

    csub

    .ClassID = cs.ClassID

    inner

    join

    dbo

    .[Subject] sub

    on

    csub

    .SubjectID = sub.SubjectID

    --where className='2 Group'

    order

    by std.[Student Name]

    You will get the required output on querying gradebok table.

    Friday, August 12, 2011 7:41 AM