Results 1 to 11 of 11
  1. #1
    Join Date
    Aug 2008
    Posts
    19

    Unanswered: Select syntax to select all items from a field

    What is the Select syntax to select all items from a field.

    I have 3 fields (“Name”, “score” and “Age”), I am using dropdown boxes to select the data which will query the database table. What I would like is to add the option “ALL” to the dropdowns so I can query the tables for all items.

    Example: Name = ALL, score = 3, Age = 23”

    Thanks

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    the syntax is a missing WHERE condition

    for example, if you want Name = ALL, score = 3, Age = 23, then your query would have
    Code:
     WHERE score = 3
       AND Age = 23
    this will automatically include all Names
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Aug 2008
    Posts
    19
    The problem is that i am using VB.NET with wizard generated table adapters and becuase the adapters are created with set querys, this is why i need to include all conditions.

    I am looking for syntax that is similar to that if searching for files (*.*) on your PC

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by Celtic101
    The problem is that i am using VB.NET with wizard generated table adapters
    it is, indeed
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    KLUDGE ALERT!!!
    Code:
    WHERE (name = <name> OR <name> = 'ALL')
    AND   (score = <score> OR <score> = 'ALL')
    Horrible, horrible performance.
    George
    Home | Blog

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    george, what happened to the Age condition?

    and by the way, only MySQL allows you to be so sloppy as to compare a numeric constant like <score> with a string constant like 'ALL'
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Well, if the OP is accepting ALL as a parameter, they are accepting all other values this way too
    Code:
    AND    (CStr(age) = <age> OR <age> = 'ALL')
    Ewwwwww!
    George
    Home | Blog

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    the real WTF here (sorry, celtic, no reflection on you, i'm just using this as a very well known catchphrase) is that the front end application seems incapable of generating what should be "bog standard" SQL
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Aug 2008
    Posts
    19
    Thanks for the help. So the following query should work
    Code:
    WHERE (name = <name> OR <name> = 'ALL')
    AND   (score = <score> OR <score> = 'ALL')
    AND   (CStr(age) = <age> OR <age> = 'ALL')

  10. #10
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by Celtic101
    So the following query should work
    what happened when you tested it?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  11. #11
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Whether it works is one thing.
    Whether it works well is a whole different question with a very simple answer - no!
    George
    Home | Blog

Posting Permissions

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