CODEWITHSUNDEEP
sqlview
Alican Kablan
Alican Kablan

Reputation: 409

Create Sql View only a table multiple times

I try create a view with one table multi time here is my code but the result is wrong.Can you help me I cant see here wrong thing.

SELECT DISTINCT 
    O1.HomeWorkId, O2.FileInfo AS TeacherFileInfo, 
    O2.Answer AS TeacherAnswer, O1.Answer AS StudentAnswer, 
    O1.StudentId
FROM            
    HomeWorkAnswer AS O1 
INNER JOIN
    HomeWorkAnswer  AS O2 ON O1.FileInfo = O2.FileInfo
WHERE        
    (O1.HomeWorkId > 0)

I save my teacher answer and student answer in this table.In my project I select two times for compare the answer.But I thing for performance it is not good and try create something like this.

Upvotes: 0

Views: 152

Answers (1)

SQLChao
SQLChao

Reputation: 7837

You can try using derived tables to separate teachers answer from students answers.

SELECT DISTINCT
  sa.HomeWorkId,
  ta.FileInfo AS TeacherFileInfo,
  ta.Answer AS TeacherAnswer,
  sa.Answer AS StudentAnswer,
  sa.StudentID
FROM
    (SELECT *
    FROM HomeWorkAnswer
    WHERE studentId = -1) ta
JOIN
    (SELECT *
    FROM HomeWorkAnswer
    WHERE teacherId = -1) sa ON ta.FileInfo = sa.FileInfo

Upvotes: 1

Related Questions