T-Sql Multiple Criteria On Where Clause

Question

Scenario

I'm using SQL Server 2005 I have a T-Sql query that has multiple criteria to search on the WHERE clause. I'm certain that I'm doing this in a very inefficient and long way round. How can I ensure that I don't have to manually type out all the criteria? Instead I want to pass in the criteria via a SELECT * clause.

Current Query Code

SELECT * FROM tblBooks
WHERE BookID = 1
OR BookID = 4
OR BookID = 5
OR BookID = 7
OR BookID = 8
OR BookID = 11
OR BookID = 14

Proposed PSEUDO Query Code

SELECT * FROM tblBooks
WHERE BookID = (SELECT BookID FROM tblAuthors WHERE Genre = 'Fiction') 

Comments On Code

As you can see from the proposed pseudo code, the idea is to select a list of BookIDs from another query and pass this list of criteria into the actual query itself. How do I go about doing this correctly?

Help greatly appreciated.

Answer 1

Use a join:

SELECT  b.*
FROM    tblBooks b
        INNER JOIN
                tblAuthors a
                ON b.BookID = a.BookID
WHERE   a.Genre = 'Fiction'

I assume these are not your real tables? I can't see why the Genre property is not on the book itself. Many authors write across multiple genres (take Iain Banks for example).

Answer 2

are you looking for something like this

SELECT * FROM tblBooks
WHERE BookID in (SELECT BookID FROM tblAuthors WHERE Genre = 'Fiction') 

This will return all the data in the table tblAuthors if book id is there

Answer 3

YOur pseudo code, isn't too pseudo. Change the = to IN and your done.

SELECT * FROM tblBooks 
WHERE BookID IN (SELECT BookID FROM tblAuthors WHERE Genre = 'Fiction')

Answer 4

Just throwing alternatives out there:

SELECT tblBooks.* 
  FROM tblBooks 
 WHERE EXISTS (SELECT *
                 FROM tblAuthors 
                WHERE tblBooks.BookID = tblAuthors.BookID
                  AND tblAuthors.Genre = 'Fiction');

---

SELECT tblBooks.*
  FROM tblBooks
 WHERE tblBooks.BookID = ANY
(SELECT tblAuthors.BookID
   FROM tblAuthors
  WHERE tblAuthors.Genre = 'Fiction');