Results 1 to 4 of 4
  1. #1
    Join Date
    Jun 2011
    Posts
    19

    Unanswered: This shoud be so easy but I'm having a frigging hard time with it... WHERE and OR

    I just want to select from the table below everything that is not from the 'Warehouse' and 'Transfers' Companies or the 'SalesDept' Companies and Location Concess

    SELECT * FROM [TestTable]
    WHERE [TheCompany] NOT IN ('Warehouse','Transfers')
    OR ([TheCompany] NOT IN ('SalesDept') AND [LocationArea] NOT IN ('Concess'))

    Stuck?

    Have I got the syntax right?
    I am searching the web but can't find any example with WHERE, AND, OR together...

  2. #2
    Join Date
    Jun 2011
    Posts
    19
    looking on the net it says this syntax should work:

    SELECT customer_id, last_name, first_name
    FROM customers
    WHERE (last_name = 'Johnson')
    OR (last_name = 'Anderson' AND state = 'California')

    so just tried this below but no luck still getting records where they have [TheCompany] = 'SalesDept' AND [LocationArea] ='Concess'

    SELECT * FROM [TestTable]
    WHERE ([TheCompany] NOT IN ('Warehouse','Transfers'))
    OR ([TheCompany] NOT IN ('SalesDept') AND [LocationArea] NOT IN ('Concess'))

  3. #3
    Join Date
    Jun 2011
    Posts
    19
    I've not been able to work this out in WHERE AND OR so I have created cte with a CASE Statement to mark the records that are:

    WHERE ([TheCompany] NOT IN ('Warehouse','Transfers'))
    OR ([TheCompany] NOT IN ('SalesDept') AND [LocationArea] NOT IN ('Concess'))

    Then Select from cte the not marked records.

    Ho Hum.

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    This is actually a logic issue instead of a syntax issue. Based on the English description, I think that you want:
    Code:
    SELECT *
       FROM [TestTable]
       WHERE [TheCompany] NOT IN ('Warehouse','Transfers')
          AND NOT ([TheCompany] = 'SalesDept') AND [LocationArea] = 'Concess')
    I really need to do a SQL Saturday presentation on this someday.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

Posting Permissions

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