Please refer to my example below for clearer understanding of what i'm doing.
Example:
Create View v AS
Select T.*, S.Name, Case When T.TESTDATE = S.STUDYDATE
Then 'Yes' else 'No' END AS TakenTest
From Test T, Student S
Where T.TESTPAPERID = '12345'
And T.StudentNo = S.StudentNo;
It create the view successfully. However, it populate duplicate rows with same values like:
TESTPAPERID StudentNo Name TakenTest
12345 6437 John Yes
12345 6437 John No
How can i resolve it as i already define that if T.TESTDATE = S.STUDYDATE then show yes. Otherwise no. And not populating the same values out.
Thanks
To have only one row, use this:
MAX(Case When T.TESTDATE = S.STUDYDATE Then 'Yes' else 'No' END)
...
GROUP BY <all other columns, which you haven't shown>
Also, this part is poorly coded:
T.TESTPAPERID = (Select TESTPAPERID From Student Where TESTPAPERID='12345')
because it is IDENTICAL to:
T.TESTPAPERID = '12345'