Results 1 to 7 of 7
  1. #1
    Join Date
    Mar 2005
    Posts
    3

    Unanswered: SELECT FROM, trouble with the WHERE bit

    HI,

    I'm sure this must be simple but I just can't get the syntax right.

    sql="select * FROM products INNER JOIN (orderitems INNER JOIN orders ON orders.orderid=orderitems.orderid) ON products.productid=orderitems.productid where ostatus<>'Cancelled' and custid=" & valid_sql(session("custid")) & " order by orders.orderid"

    That works OK but I want ostatus<>'Cancelled OR 'Uncompleted'

    Anyone?

  2. #2
    Join Date
    May 2004
    Posts
    125
    you can do this in a few different ways.

    1: ostatus<>'Cancelled' OR ostatus<> 'Uncompleted'
    2: ostatus not in ('Cancelled', 'Uncompleted')

    HTH

  3. #3
    Join Date
    Mar 2005
    Posts
    3
    Thanks

    Method 1 doesn't work, I had tried that it just goes into a loop.
    Method 2 works fine though.

  4. #4
    Join Date
    Nov 2003
    Location
    Christchurch, New Zealand
    Posts
    1,618
    to get 1 to work....
    Code:
    1: ostatus<>'Cancelled' OR ostatus<> 'Uncompleted'
    should be
    Code:
    1: ostatus<>'Cancelled' AND ostatus<> 'Uncompleted'
    status must not = Cancelled and Status must not = Uncompleted. if it is or then the Cancelled's <> Uncompleted so they pass and visa versa.

  5. #5
    Join Date
    May 2004
    Posts
    125
    Thanks rokslide...that was my bad.

  6. #6
    Join Date
    Mar 2005
    Posts
    3
    1: ostatus<>'Cancelled' AND ostatus<> 'Uncompleted'
    Thats not correct, there are no records that are both cancelled and uncompleted, they are either one or the other, or something else. If I use that it returns all records.

  7. #7
    Join Date
    Nov 2003
    Location
    Christchurch, New Zealand
    Posts
    1,618
    Code:
    1: ostatus<>'Cancelled' AND ostatus<> 'Uncompleted'
    using the above should return those that are (as you put it) "something else". If it is returning everything then I would be tempted to suggest that there is a flaw somewhere else in your query. It's possible that you need to wrap this section in brackets or something else, it really depends on what is happening...

Posting Permissions

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