Multiple columns in subquery: SQLite, MS SQL Server

Question

How to rewrite this SQL query SQLFiddle without using multiple column selection in WHERE section.

Answer 1

The syntax (hs1.ID,hs2.ID,hs3.ID) IN ...

Is not supported in SQL Server.

You can only have 1 column or variable on the left side of an IN statement.

You can rewrite it using EXISTS:

SELECT hs1.name
    ,hs1.grade
    ,hs2.name
    ,hs2.grade
    ,hs3.name
    ,hs3.grade
FROM highschooler AS hs1
    ,highschooler AS hs2
    ,highschooler AS hs3
WHERE EXISTS (
        SELECT 1
        FROM friend AS fr1
        JOIN (
            SELECT ls.ID1
                ,ls.ID2
            FROM likes AS ls
            WHERE  NOT EXISTS (
                    SELECT f.ID1
                        ,f.ID2
                    FROM friend AS f
                    WHERE f.ID1 = ls.ID1
                    AND f.ID2 = ls.ID2
                    )
            ) AS X ON fr1.ID1 = X.ID1
        JOIN friend AS fr2 ON X.ID2 = fr2.ID1
        WHERE fr1.ID2 = fr2.ID2
            AND hs1.ID = X.ID1
            AND hs2.ID = X.ID2
            AND hs3.ID = fr1.ID2
        )

SQL Fiddle in SQL Server