sql query problem

yuliam
01-14-2004, 07:16 AM
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???

davie
01-14-2004, 07:28 AM
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'))

Dennis DVR
01-14-2004, 07:33 AM
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')

yuliam
01-14-2004, 07:33 AM
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

yuliam
01-14-2004, 07:36 AM
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

00100b
01-14-2004, 07:36 AM
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'

yuliam
01-14-2004, 07:48 AM
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)

MKoslof
01-14-2004, 07:52 AM
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.

davie
01-14-2004, 07:55 AM
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')

00100b
01-14-2004, 07:55 AM
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.

00100b
01-14-2004, 07:56 AM
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')

yuliam
01-14-2004, 08:07 AM
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....

Dennis DVR
01-14-2004, 08:21 AM
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.

EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum