Retrieve Data from Different Tables (SQL Server)

Question

I have 4 tables in a SQL Server database with following schema:

Attendance

CREATE TABLE [dbo].[Attendance] (
[AttendanceId] UNIQUEIDENTIFIER DEFAULT (newid()) NOT NULL,
[CourseId]     UNIQUEIDENTIFIER NOT NULL,
[StudentId]    UNIQUEIDENTIFIER NOT NULL,
[SubjectId]    UNIQUEIDENTIFIER NOT NULL,
[Semester]     INT              NOT NULL,
[Month]        NVARCHAR (50)    NOT NULL,
[Count]        INT              NOT NULL,
CONSTRAINT [PK_Attendance] PRIMARY KEY NONCLUSTERED ([AttendanceId] ASC),
CONSTRAINT [FK_Attendance_Student] FOREIGN KEY ([StudentId]) REFERENCES [dbo].[Student] ([StudentId]) );

Course

CREATE TABLE [dbo].[Course] (
[CourseId] UNIQUEIDENTIFIER DEFAULT (newid()) NOT NULL,
[Name]     NVARCHAR (50)    NOT NULL,
CONSTRAINT [PK_Course] PRIMARY KEY NONCLUSTERED ([CourseId] ASC)
);

Student

CREATE TABLE [dbo].[Student] (
[StudentId] UNIQUEIDENTIFIER DEFAULT (newid()) NOT NULL,
[CourseId]  UNIQUEIDENTIFIER NOT NULL,
[Name]      NVARCHAR (100)   NOT NULL,
[RollNo]    INT              NOT NULL,
[Semester]  INT              NOT NULL,
CONSTRAINT [PK_Student] PRIMARY KEY NONCLUSTERED ([StudentId] ASC),
CONSTRAINT [FK_Student_Course] FOREIGN KEY ([CourseId]) REFERENCES [dbo].[Course] ([CourseId])
);

Subject

CREATE TABLE [dbo].[Subject] (
[SubjectId] UNIQUEIDENTIFIER DEFAULT (newid()) NOT NULL,
[CourseId]  UNIQUEIDENTIFIER NOT NULL,
[Name]      NVARCHAR (100)   NOT NULL,
[Semester]  INT              NOT NULL,
CONSTRAINT [PK_Subject] PRIMARY KEY NONCLUSTERED ([SubjectId] ASC),
CONSTRAINT [FK_Subject_Course] FOREIGN KEY ([CourseId]) REFERENCES [dbo].[Course] ([CourseId])
);

I need to create a attendance report in the following format:

Please tell me what SQL Query I need to use and if there's any change in schema required then suggest the same.

I'm looking forward to have your replies.

Thanks,

I've tried the following query: SELECT Course.Name, Student.Name, Subject.Name, Attendance.Semester, Attendance.Month, Attendance.Count FROM Course, Student, Subject, Attendance The problem with this is it is retrieving all records from Student, Subject and Course tables but I want records only for subjects for which the attendance is stored. — Shyam Singh
– Shyam Singh, Commented Mar 24, 2014 at 11:02
Thanks buddy, I've tried INNER JOIN and it led me to achieving the exact query I needed. Thanks for pointing out this aspect! — Shyam Singh
– Shyam Singh, Commented Mar 24, 2014 at 11:32

Starscream1984 · Accepted Answer · 2014-03-24 11:13:31Z

1

You need to use a JOIN in your query so that it only returns rows which match a [StudentId] from the Attendance table.

e.g.

SELECT c.CourseName, s.StudentName, u.SubjectName, u.Semester, a.Month, a.Count
FROM Student s 
    JOIN Attendance a ON s.StudentId = a.StudentId
    JOIN Course c ON a.CourseId = c.CourseId
    JOIN Subject u ON c.CourseId = u.CourseId

Something along these lines will only return rows which specifically match

edited Mar 24, 2014 at 11:13

answered Mar 24, 2014 at 11:06

Starscream1984

3,0621 gold badge21 silver badges27 bronze badges

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

4 Comments

Shyam Singh Over a year ago

Oh, I'm sorry but I didn't Vote Down buddy and thanks for your reply, it worked with a little adjustment. I'm marking it as Answer. Thanks again!

Shyam Singh Over a year ago

Can you also tell me how do I create a View from this Query?

Starscream1984 Over a year ago

read: w3schools.com/sql/sql_view.asp - should be the same as creating view with any query

Mike Over a year ago

To create a view, use CREATE VIEW view_name AS SELECT xxx where xxx is your working SELECT statement

Collectives™ on Stack Overflow

Retrieve Data from Different Tables (SQL Server)

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related