SQL server 2005, syntax error

Question

This line do not work: (the problem is in the last line)

SELECT Book_Name  
FROM Books  
WHERE (Year, Faculty) = (SELECT Year, Faculty FROM Books WHERE Book_Id = 1112);

How can I fix it?

Answer 1

    SELECT b.Book_Name  
      FROM Books b
INNER JOIN (Select Year, Faculty From Books Where Book_Id = 1112) b2
        ON b.Year = b2.Year and b.Faculty = b2.Faculty

Without Inner Join the alternative is

SELECT b.Book_Name
  FROM Books b
 WHERE Year In (Select Year From Books Where Book_ID = 1112)
   AND Faculty In (Select Faculty From Books Where Book_ID = 1112)

However, if it is homework you should really work this out for yourself :-p

Answer 2

A join is best, but if you can't (because of homework restriction) ,try this:

SELECT
    Book_Name  
    FROM Books  
    WHERE
        Year IN (SELECT Year FROM Books WHERE Book_Id = 1112)
        AND Faculty IN (SELECT Faculty FROM Books WHERE Book_Id = 1112)