PLZ help: problem with a certain query

I have this easy query , but the MSSQL server give me an error (Incorrect syntax near ',')

my query is:

--

SELECT Book_Name FROM Books

WHERE (Year,Faculty) =

(SELECT Year,Faculty FROM Books

WHERE Book_Id =100);

--

its seems the error because this line: WHERE (Year,Faculty) =

I don't what the problem!! this query is legal in mysql but not in mssql.

when I tried this one:

SELECT Book_Name FROM Books

WHERE Year =

(SELECT Year FROM Books

WHERE Book_Id =100);

--

its succeed ...

why the first not?

please help or give me any advice....

thanks in advance

Use the following query...

SELECT Book_Name FROM Books Join

(SELECT Year,Faculty FROM Books

WHERE Book_Id =100) D on Books.Year = D.Year and Books.Faculty = D.Faculty

|||

that great ..... thanks you.

but what I can do if I want to use "not in" instead of "=":

the new query will be:

--

SELECT Book_Name FROM Books
WHERE (Year,Faculty) not in
(SELECT Year,Faculty FROM Books
WHERE Book_Id =100);

-

|||

use the following query..

SELECT Book_Name FROM Books Left Outer Join

(SELECT Year,Faculty FROM Books

WHERE Book_Id =100) D on Books.Year = D.Year and Books.Faculty = D.Faculty

Where D.Year is NULL or D.Faculty is NULL

-or

SELECT Book_Name FROM Books Where Cast(Year as varchar) + '-' + Cast(Faculty as varchar) NOT IN

(SELECT Cast(Year as varchar) + '-' + Cast(Faculty as varchar) FROM Books

WHERE Book_Id =100)

|||

thank you a lot.

Worked for me