locked
SQL Statement RRS feed

  • Question

  • Hi all,

     

    I would like to build a OR condition in my sql statement.

    The OR condition is based on the field BookID whereby it is not fixed.

    How to build a  SQL statement whereby the OR condition is not fixed?

     

    For example, first run, BookId= '1', BookId='2'

    second run BookId='1', BookId='2', BookId='3'

     

    and my table name is 'BookInfo'

     

    select * from BookInfo whereby BookId='1' or BookId='2' .....

     

    Please help.

     

    Thanks

     

    Wednesday, June 6, 2007 3:46 AM

Answers

  • Instead of using OR you could also use the IN operator

    SELECT * FROM BookInfo WHERE BookId IN ( 1 , 2, 9 );
    Wednesday, June 6, 2007 6:03 AM
  • You could also use BETWEEN

     

    SELECT * FROM BookInfo WHERE BookId BETWEEN 1 AND 3

     

    With this approach, you can pass the numbers per parameter

    Wednesday, June 6, 2007 6:41 AM
  • A flexible approrach would be to have a table of bookIds that satisfy the condition you want , and then you can use a join

    eg:

     

    have a BookSearch table:

    CREATE TABLE BookSearch (BookId int NOT NULL)

     

    fill it with the books you want to search for eg

    INSERT BookSearch VALUES (1)

    INSERT BookSearch VALUES (2)

    INSERT BookSearch VALUES (99)

    INSERT BookSearch VALUES (87)

    etc, etc

     

    then you can get the book info you want from:

    SELECT

       bi.*

    FROM

       BookInfo bi

       JOIN BookSearch bs

          ON bi.BookId = bs.BookId

     

    In reality, you'd might also want the BookSearch table to have a SearchId in it (which you'd have in a WHERE clause), so that you could retain searches. It would also mean that you wouldn't need to clear down the BookSearch table for differing searches

    Friday, June 8, 2007 8:20 PM

All replies

  • Instead of using OR you could also use the IN operator

    SELECT * FROM BookInfo WHERE BookId IN ( 1 , 2, 9 );
    Wednesday, June 6, 2007 6:03 AM
  • You could also use BETWEEN

     

    SELECT * FROM BookInfo WHERE BookId BETWEEN 1 AND 3

     

    With this approach, you can pass the numbers per parameter

    Wednesday, June 6, 2007 6:41 AM
  • A flexible approrach would be to have a table of bookIds that satisfy the condition you want , and then you can use a join

    eg:

     

    have a BookSearch table:

    CREATE TABLE BookSearch (BookId int NOT NULL)

     

    fill it with the books you want to search for eg

    INSERT BookSearch VALUES (1)

    INSERT BookSearch VALUES (2)

    INSERT BookSearch VALUES (99)

    INSERT BookSearch VALUES (87)

    etc, etc

     

    then you can get the book info you want from:

    SELECT

       bi.*

    FROM

       BookInfo bi

       JOIN BookSearch bs

          ON bi.BookId = bs.BookId

     

    In reality, you'd might also want the BookSearch table to have a SearchId in it (which you'd have in a WHERE clause), so that you could retain searches. It would also mean that you wouldn't need to clear down the BookSearch table for differing searches

    Friday, June 8, 2007 8:20 PM