Results 1 to 5 of 5
  1. #1
    Join Date
    Apr 2004
    Location
    UK
    Posts
    40

    Unanswered: where clause that uses result of another query?

    Hi,

    I'm looking to refine ways i've written my current application to make it more efficient.
    Is it possible to write a query who's WHERE parameter is the result of another query without using a procedure/function?
    Use the classic Product details table as an example

    My query currently looks like
    SELECT productID, productDescription, productPrice from tblProducts where productPrice >= (Select productPrice from tblProducts where productID = 7)

    This is probably a bad example but the underlined part of that query show what i'm trying to achieve.
    Is it possible to use another query as part of the WHERE clause?
    At the moment i'm getting syntax errors.

    Many Thanks

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    yes it is possible, we do this all the time
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Apr 2004
    Location
    UK
    Posts
    40
    So is the syntax I used above correct?

  4. #4
    Join Date
    Apr 2004
    Location
    UK
    Posts
    40
    nvm - according to this article I wouldn't be able to alter the table in my query anyway (I was hoping to use this to write an UPDATE query) because i'd be using a subquery that looked up the same table I was trying to update.

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by Superfly1611
    So is the syntax I used above correct?
    yep, it is
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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