Where clause only return one condition instead two using OR

Question

I have a simple select statement like:

SELECT *
FROM [customer] AS [c]
    INNER JOIN [customertype] AS [ct] ON [c].[CustTypeKey] = [ct].[CustTypeKey]
    INNER JOIN [ProjectCustomer] AS [pc] ON [c].[CustomerKey] = [pc].[CustomerKey]
    INNER JOIN [Project] AS [p] ON [pc].[ProjectKey] = [p].[ProjectKey]
    INNER JOIN [Address] AS [A] ON [P].ProjectGuid = [A]. [AddressGuid]
WHERE [ct].[CustTypeKey] = 7
    AND [c].[Name] = 'Customer'
    AND [A].[RegionKey] = 2
    OR [A].[RegionKey] = 3

For some reason the WHERE clause is not working correctly. I filter by [A].[RegionKey] = 2 and [A].[RegionKey] = 3. So I want to get all items who have RegionKey 2 and 3, but it only get values with RegionKey 3, and I'm sure I have items with RegionKey 2 too.

What am I doing wrong with this WHERE clause?

Possible duplicate of [SQL Logic Operator Precedence: And and Or](https://stackoverflow.com/questions/1241142/sql-logic-operator-precedence-and-and-or) — Ryan Cogswell, Nov 14 '18 at 22:30
Check out the link @RyanC added. Just tweak your where clause and put parentheses around your regionkey ([A].[RegionKey] = 2 OR [A].[RegionKey] = 3) or simply use [A].[RegionKey] IN (2,3) which might be cleaner. — Tim Mylott, Nov 14 '18 at 22:31

Joel Coehoorn · Accepted Answer · 2018-11-14T22:37:25.063

1

Change this:

AND [A].[RegionKey] = 2
   OR [A].[RegionKey] = 3

to this:

AND ([A].[RegionKey] = 2
   OR [A].[RegionKey] = 3)

or this:

AND [A].[RegionKey] IN (2,3)

Then make sure the other conditions in the WHERE clause are correct, as it's likely at least one of them is over-filtering.

edited Nov 14 '18 at 22:37

answered Nov 14 '18 at 22:36

Joel Coehoorn

399,467
113
570
794

When I do that I don't receive any results – Jonathan Nov 14 '18 at 22:37
Correct. You may have records where the `RegionKey` is `2`, but you don't have any where RegionKey is 2 **AND** all of the conditions are on the other tables are true. – Joel Coehoorn Nov 14 '18 at 22:39

Bobert · Answer 2 · 2018-11-14T22:37:31.490

SELECT
                    *
                    FROM [customer] AS [c]
                        INNER JOIN [customertype] AS [ct] ON [c].[CustTypeKey] = [ct].[CustTypeKey]
                        INNER JOIN [ProjectCustomer] AS [pc] ON [c].[CustomerKey] = [pc].[CustomerKey]
                        INNER JOIN [Project] AS [p] ON [pc].[ProjectKey] = [p].[ProjectKey]
                        INNER JOIN [Address] AS [A] ON [P].ProjectGuid = [A]. [AddressGuid]
                    WHERE [ct].[CustTypeKey] = 7
                        AND [c].[Name] = 'Customer'
                         AND ([A].[RegionKey] = 2 OR [A].[RegionKey] = 3)

You need to add () around the two items to be affected by the OR clause. Right now the OR clause is invalidating the previous AND statements.

Your previous query was saying this in plain english: "Return all the results that have a custtype key of 7 AND a name of Customer AND a Region key of 2, or just the results that have a region key of 3 (so if the region key is 3, they don't have to be a customer or have a custtype of 7).

Your code should run correctly now.

I don't think you have any customers with a custtypekey of 7 and name of customer and regionkey of 2. It would show up otherwise. Possibly the INNER JOINS are an issue? Do any of customers with a region key 2 not exists in any of the tables? The INNER JOINS mean the customers details must exist in all the tables, not just one. — Bobert, Nov 14 '18 at 22:40

Where clause only return one condition instead two using OR

2 Answers2