sql query problem

I'm using SQL Server 2000 and VB 6 interface,
writing sql query like:

SELECT Book_Name FROM Books
WHERE (Year, Faculty) = (SELECT Year, Faculty FROM Books WHERE Book_Id = '1')

I'm getting the error: "Incorrect synrax near ','"

I think it doesn't whant to compare multiply values(
because the following query works:
SELECT Book_Name FROM Books
WHERE (Year) = (SELECT Year FROM Books WHERE Book_Id = '1')

Where could be the problem???

have you tried

SELECT Book_Name FROM Books
WHERE (Year = (SELECT Year FROM Books WHERE Book_Id = '1'))
AND (Faculty = (SELECT Faculty FROM Books WHERE Book_Id = '1'))

SELECT Book_Name FROM Books
WHERE Year in (SELECT Year FROM Books WHERE Book_Id = '1') and
Faculty in (SELECT Faculty FROM Books WHERE Book_Id = '1')

have you tried

SELECT Book_Name FROM Books
WHERE (Year = (SELECT Year FROM Books WHERE Book_Id = '1'))
AND (Faculty = (SELECT Faculty FROM Books WHERE Book_Id = '1'))


Thank you for answering...
This will not return the same result,
because it will return all combinations of year and faculty, and i need only faculties that relevant to this specific year

have you tried

SELECT Book_Name FROM Books
WHERE (Year = (SELECT Year FROM Books WHERE Book_Id = '1'))
AND (Faculty = (SELECT Faculty FROM Books WHERE Book_Id = '1'))


Thank you for answering...
This will not return the same result,
because it will return all combinations of year and faculty, and i need only faculties that relevant to this specific year

Again, to explain myself better... the key in table is year, faculty,
so i can't separate them

I think that you may need to do a join on a second instance of the table.

SELECT T1.Book_Name
FROM Books T1, Books T2
WHERE T1.Year = T2.Year AND
T1.Faculty = T2.Faculty AND
T2.Book_id = '1'

I think that you may need to do a join on a second instance of the table.

SELECT T1.Book_Name
FROM Books T1, Books T2
WHERE T1.Year = T2.Year AND
T1.Faculty = T2.Faculty AND
T2.Book_id = '1'


I'm sorry, i don't understand your answer... as far as i see this is equivalent to SELECT Book_Name FROM Books (because Year and Faculty is a primary key)

He is joining together TWO tables where the faculty field value is identical. So, if both tables have the same value within the faculty field the condition is met and those records are valid. This is correctly pulling data from the two tables you need.

What about:


SELECT Book_Name
FROM Books AS TB1 INNER JOIN Books AS TB2 ON (TB1.Year = TB2.Year) AND (TB1.Faculty = TB2.Faculty)
WHERE (TB2.Book_id = '1')

Your original post was where you were trying to use a sub-query of the same table as a WHERE condition for the selection of the records from the same table. As you have found, that form of syntax will not work.

The syntax that I posted would do this by joining the table to itself based on the fields that you wish to be equal between the two and then applying the criteria to Book_id in the joined instance of the table.

Maybe you should describe in more detail what the query should be accomplishing.

The only real difference between this example and my previous example is that you are using an explicit inner join where I was using an implied inner join. Both would produce the same results (in this instance).
What about:


SELECT Book_Name
FROM Books AS TB1 INNER JOIN Books AS TB2 ON (TB1.Year = TB2.Year) AND (TB1.Faculty = TB2.Faculty)
WHERE (TB2.Book_id = '1')

The only real difference between this example and my previous example is that you are using an explicit inner join where I was using an implied inner join. Both would produce the same results (in this instance).
What about:


SELECT Book_Name
FROM Books AS TB1 INNER JOIN Books AS TB2 ON (TB1.Year = TB2.Year) AND (TB1.Faculty = TB2.Faculty)
WHERE (TB2.Book_id = '1')

I'll describe more explicity my problem:
the real query is :
sqlTxt = "SELECT * FROM Factors WHERE ([Factor ID] , [Factor Version]) " & _
"IN (SELECT DISTINCT Factors.[Factor ID] , Factors.[Factor Version] " & _
"FROM Factors, Levels WHERE " & _
"(Factors.[Factor ID] = Levels.[Factor ID]) AND " & _
" (Factors.[Factor Version] = Levels.[Factor Version]) AND...
The key is id and version, but i can't choose all fields (Select *) and use command DISTINCT, because some of the fields are text.
i found in tutorials the query i posted at the beginning, i think it's equal to mine....

I think it would be more helpful if you will give us some example record of factors and level tables and output that you expect from the query.