SitePoint Sponsor

User Tag List

Results 1 to 10 of 10
  1. #1
    Xbox why have you forsaken me? moospot's Avatar
    Join Date
    Feb 2001
    Location
    Clearwater, FL
    Posts
    3,615
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Building a query using multiple selects

    Okay, this project is turning out to be a bear.

    I need to build a query using mulitple select boxes. I have 4 options I need to select from and apparently in SQL I need to run a separate query for each option that is selected.

    For example if I wanted to select Reebok and Saucony running shoes in size 9 and with white in the color :

    Code:
    SELECT Brand, category, ShoeSize, ShoeColor, Description, InFlyer
    FROM Flyer WHERE (((Brand)="REEBOK") AND ((category)="RUNNING") AND ((ShoeSize)="9") AND ((ShoeColor) Like "*White*")) OR (((Brand)="SAUCONY") AND ((category)="RUNNING") AND ((ShoeSize)="9") AND ((ShoeColor) Like "*White*"));

    So if I wanted to select two brands in 5 sizes in 2 categories and all colors, this could be a very, very large query with potentially dozens of WHERE conditions.

    My question is how can I loop through all the items covering all the selections?

    Note: This will be used by one person as a desktop application, not on a web server.

  2. #2
    Just Blow It bronze trophy
    DaveMaxwell's Avatar
    Join Date
    Nov 1999
    Location
    Mechanicsburg, PA
    Posts
    7,254
    Mentioned
    113 Post(s)
    Tagged
    1 Thread(s)

    Re: Building a query using multiple selects

    You can't specify different criteria for different shoes can you (ie, you can't say "Give me all the blue size 5 reeboks and all the white size 6 nikes", can you?)

    If not, then couldn't you make the select like this?

    Code:
    SELECT Brand, Category, ShoeSize, ShoeColor
    WHERE Brand IN ('REEBOK','Saucony')
      AND Category IN ('RUNNING')
      AND ShoeSize IN (9)
      AND ShoeColor like '%white%'
    If you use in, you can specify one or more values in the select and it will work. If you wanted all 9 and 9.5 shoes, your select would then be:
    Code:
    SELECT Brand, Category, ShoeSize, ShoeColor
    WHERE Brand IN ('REEBOK','Saucony')
      AND Category IN ('RUNNING')
      AND ShoeSize IN (9,9.5)
      AND ShoeColor like '%white%'
    Dave Maxwell - Manage Your Site Team Leader
    My favorite YouTube Video! | Star Wars, Dr Suess Style

  3. #3
    Xbox why have you forsaken me? moospot's Avatar
    Join Date
    Feb 2001
    Location
    Clearwater, FL
    Posts
    3,615
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Mr. Maxwell, you are a lifesaver!

    Do I have to specify the tablename in the query?

    SELECT blah FROM table
    WHERE ,......
    Last edited by moospot; Mar 22, 2002 at 13:53.

  4. #4
    Just Blow It bronze trophy
    DaveMaxwell's Avatar
    Join Date
    Nov 1999
    Location
    Mechanicsburg, PA
    Posts
    7,254
    Mentioned
    113 Post(s)
    Tagged
    1 Thread(s)
    DOH!!! Yes, you do the normal select FROM....too quick on the copy and paste. Sorry about that...
    Dave Maxwell - Manage Your Site Team Leader
    My favorite YouTube Video! | Star Wars, Dr Suess Style

  5. #5
    Xbox why have you forsaken me? moospot's Avatar
    Join Date
    Feb 2001
    Location
    Clearwater, FL
    Posts
    3,615
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Originally posted by DaveMaxwell
    DOH!!! Yes, you do the normal select FROM....too quick on the copy and paste. Sorry about that...
    Hey, we all make mistakes, don't we?!?

  6. #6
    Just Blow It bronze trophy
    DaveMaxwell's Avatar
    Join Date
    Nov 1999
    Location
    Mechanicsburg, PA
    Posts
    7,254
    Mentioned
    113 Post(s)
    Tagged
    1 Thread(s)
    Too many somedays.....
    Dave Maxwell - Manage Your Site Team Leader
    My favorite YouTube Video! | Star Wars, Dr Suess Style

  7. #7
    Xbox why have you forsaken me? moospot's Avatar
    Join Date
    Feb 2001
    Location
    Clearwater, FL
    Posts
    3,615
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    One more question. Can I use multiple LIKE statements like so?

    Code:
    SELECT Brand, Category, ShoeSize, ShoeColor
    WHERE Brand IN ('REEBOK','Saucony')
      AND Category IN ('RUNNING')
      AND ShoeSize IN (9,9.5)
      AND ShoeColor like ('%white%','%black%')

  8. #8
    Just Blow It bronze trophy
    DaveMaxwell's Avatar
    Join Date
    Nov 1999
    Location
    Mechanicsburg, PA
    Posts
    7,254
    Mentioned
    113 Post(s)
    Tagged
    1 Thread(s)
    I don't think so. You would probably have to do something like this:

    Code:
    SELECT Brand, Category, ShoeSize, ShoeColor
    WHERE Brand IN ('REEBOK','Saucony')
      AND Category IN ('RUNNING')
      AND ShoeSize IN (9,9.5)
      AND (ShoeColor like ('%white%')
           OR ShoeColor like ('%black%'))
    Dave Maxwell - Manage Your Site Team Leader
    My favorite YouTube Video! | Star Wars, Dr Suess Style

  9. #9
    Xbox why have you forsaken me? moospot's Avatar
    Join Date
    Feb 2001
    Location
    Clearwater, FL
    Posts
    3,615
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    That did the trick! Thanks again. You don;t know how much time you saved me

  10. #10
    Just Blow It bronze trophy
    DaveMaxwell's Avatar
    Join Date
    Nov 1999
    Location
    Mechanicsburg, PA
    Posts
    7,254
    Mentioned
    113 Post(s)
    Tagged
    1 Thread(s)
    Not a problem. Glad to help!!
    Dave Maxwell - Manage Your Site Team Leader
    My favorite YouTube Video! | Star Wars, Dr Suess Style


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •