Оператор select и самосоединение

SELECT, который реализует самосоединение:

-- FIND PAIRS OF TESTCASES WITH EQUAL AMOUNT OF QUESTIONS --

SELECT t1.Name as 'Name of t1', t2.Name as 'Name of t2', t1.Questions_Amount

FROM dbo.TestCase t1, dbo.TestCase t2

WHERE t1.Questions_Amount = t2.Questions_Amount AND t1.Name < t2.Name

ORDER BY LEN(t1.Name) ASC, LEN(t2.Name) ASC;

-- The same but we're searching for triples (not pairs) with the same creator --

SELECT t1.Name as 'Name of t1', t2.Name as 'Name of t2', t3.Name as 'Name of t2', a.Full_Name

FROM dbo.TestCase t1, dbo.TestCase t2, dbo.TestCase t3, dbo.Account a

WHERE t1.Id_Creator = t2.Id_Creator AND t2.Id_Creator = t3.Id_Creator AND t1.Id < t2.Id AND t2.Id < t3.Id AND t1.Id_Creator = a.Id

ORDER BY LEN(t1.Name) ASC, LEN(t2.Name) ASC, LEN(t3.Name) ASC;

-- FIND PAIRS OF QUESTIONS WITH THE SAME RIGHT ANSWERS, AND SHOW WHO CREATED THEM --

SELECT q1.Name as 'Name of t1', 'Author of t1' = a1.Full_Name, q2.Name as 'Name of t2', 'Author of t2' = a2.Full_Name, q1.Right_Answers

FROM dbo.Question q1, dbo.Question q2, dbo.TestCase t1, dbo.TestCase t2, dbo.Account a1, dbo.Account a2

WHERE q1.Right_Answers = q2.Right_Answers AND q1.Id < q2.Id AND q1.Id_Test = t1.Id AND q2.Id_Test = t2.Id AND t1.Id_Creator = a1.Id AND t2.Id_Creator = a2.Id

ORDER BY LEN(q1.Name) ASC, LEN(q2.Name) ASC;