Q:

How can I rewrite a query with joins to show the criteria which was not included in my search?

Here I have another query bothering me.
Select S.ID,S.Name,S.Surname,B.Title,SB.DateAndTimeIssued FROM Students S
INNER JOIN StudentBooks SB ON
S.ID = SB.StudentID
INNER JOIN Books B
ON B.ID = SB.BookID
WHERE B.ID = @BookID

Results:
1 | asd   | asd   | Book      | 2011-10-12 18:31:40.557
1 | asd   | asd   | Gray Book | 2011-10-12 18:36:26.950
1 | asd   | asd   | Gray Book | 2011-10-12 18:36:34.137

Explanation : This query sends me back the names and surnames of all students which had a book with a specific ID Issued to them. The date and the book issued to them are also displayed.
The problem is that these books in the table above have only been assigned to one student. How can I rewrite this query to display to me which students have not received a specific book with the id of @BookID ? When I use WHERE NOT B.ID = @BookID it only sends me the name of the students which had not received the book , but has received a book in the past.
My attempt:
SELECT S.Name,S.Surname FROM Students S
INNER JOIN StudentBooks SB
ON SB.StudentID = S.ID
INNER JOIN Books B
ON B.ID = SB.BookID
WHERE NOT SB.BookID = @BookID

I need the names of the students which had not received the book at all. Students, StudentBooks and Books are all seperate tables, Students and Studentbooks happen to be in a many-to-many relationship, since a book can be re-issued to them on a later date, or a copy of the book can be re-issued to them incase they lost theirs

A:

I guess you can combine the books and student books table and get all the ids of students who took a book and then look for any id in student table that is not present in the combination of the ids you get from first table ( books and student books)
SELECT S.ID,S.Name,S.Surname,B.Title,SB.DateAndTimeIssued 
FROM Students S 
WHERE s.id NOT IN (SELECT id 
                   FROM Books b 
                   JOIN StudentBooks sb 
                   ON b.ID = sb.BookID
                   WHERE b.ID = @BookID)

