Create Sql View only a table multiple times

Question

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.

If you just want to see an exact copy of the table multiple times you could use a union. w3schools.com/sql/sql_union.asp What exactly do you want the output to be? — Connor
– Connor, Commented Nov 2, 2017 at 18:55
Can you show us HomeWorkAnswer? How can you tell which row is a teacher answer vs a student answer? That's what you need to fix in your join — SQLChao
– SQLChao, Commented Nov 2, 2017 at 18:58
Yess thank you I never thought Union for solving my problem :) Here is one example I find it now , stackoverflow.com/questions/12026555/… — Alican Kablan
– Alican Kablan, Commented Nov 2, 2017 at 18:58
@SQLChao I have studentId and teacherId field in table , If student is giving answer I save teacherId =(-1) , If teacher is giving answer I save studentId= (-1) — Alican Kablan
– Alican Kablan, Commented Nov 2, 2017 at 19:00

SQLChao · Accepted Answer · 2017-11-02 19:09:38Z

1

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

answered Nov 2, 2017 at 19:09

SQLChao

7,8851 gold badge19 silver badges33 bronze badges

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

Collectives™ on Stack Overflow

Create Sql View only a table multiple times

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related