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     开发者_JAVA百科 | 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

---

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)

---

Outer join to get all the students, maybe something like:

Select S.ID,S.Name,S.Surname,B.Title,SB.DateAndTimeIssued
FROM Students S
LEFT OUTER JOIN StudentBooks SB 
    ON S.ID = SB.StudentID
    AND SB.BookId = @BookId
LEFT OUTER JOIN Books B
    ON B.ID = SB.BookID

Which won't include title for those students without an Student Book record, if you want a title for every record:

Select S.ID,S.Name,S.Surname,B.Title,SB.DateAndTimeIssued
FROM Students S
CROSS JOIN (select title from Books where Id = @BookId) B
LEFT OUTER JOIN StudentBooks SB
    on SB.StudentId = S.ID
    and SB.BookId = @BookId

But I don't understand results you show:

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

As I understand your query, the result should be for one particular book based on Book.Id = @BookID, but you are showing two different book titles, "Book" and "Gray Book".

As written, neither of these queries generalizes to getting records for more than one book.id at a time.