CODEWITHSUNDEEP
sqlsql-servert-sql
stepheniok
stepheniok

Reputation: 405

AND / OR logic in WHERE statement

I'm attempting to select from tblReportDescription. I have a column reportType that I am using in my logic in the WHERE statement.

I would like to SELECT ALL reportType = 1 where the length of the column descriptionCode is equal to 1 or is equal to 4.

I would also like to SELECT all reportType = 2 WHERE the length is less then 3 or that is greater then 5.

I'm attempting this logic with this line of code here:

SELECT * FROM tblReportDescription
WHERE (reportType = 1 and LEN(descriptionCode) = 4 OR LEN(descriptionCode) = 1) OR (
reportType = 2 and LEN(descriptionCode) < 3 OR LEN(descriptionCode) > 5
)

It seems like the OR reportType = 2 and LEN(descriptionCode) < 3 OR LEN(descriptionCode) > 5 logic is interfering with the reportType = 1 logic. The reason for this is that one of the rows that are return has a descriptionCode length of 7 when it should only be returning lengths of 4 and 1.

Here is an example of my query:

SELECT *
into tblReportDescription
FROM
(
    values 
    (34, 'Report tile 1', '3223', 1),
    (21, 'Report tile 2', '2', 1),
    ( 3, 'Report Title 3', '344322', 2),
    (13, 'Report Title 4', '34', 2),
    ( 8, 'Report Title 5', '1511', 1),
    ( 9, 'Report Title 6', '15', 2),
    (19, 'Report Title 7', '4444', 2),
    (14, 'Report Title 8', '6662566', 1)
  ) v ( id, description, descriptionCode, reportType )

SELECT
    *
FROM
    tblReportDescription
WHERE
    (
        reportType = 1
        and
        LEN(descriptionCode) = 4
        OR
        LEN(descriptionCode) = 1
    )
    OR
    (
        reportType = 2
        and
        LEN(descriptionCode) < 3
        OR
        LEN(descriptionCode) > 5
    )

and here is an example of the select that is returned:

id description descriptionCode reportType
34 Report Title 3223 1
21 Reports Title 2 2 1
3 Report Title 3 344322 2
13 Report Title 4 34 2
8 Report Title 5 1511 1
9 Report Title 6 15 2
14 Report Title 8 6662566 1

If the query is correct I should NOT be seeing row Id 14 where length description code = 7 since it's a report type of 1 which should only return lengths of 4 and 1

How can I correct my WHERE statement so it's not interfering with one another?

Here is a database fiddle with my examples

Upvotes: 0

Views: 871

Answers (3)

Gudwlk
Gudwlk

Reputation: 1157

Try this code:

Here you can use UNION or UNION ALL. I suggest UNION ALL because you know that the given data set does not include duplicates. But if the data set is subject to change better to use UNION as it filters Duplicates.

Make note that UNION ALL is better in performance than UNION due to that UNION filters duplicates.

 SELECT * 
 FROM tblReportDescription
 WHERE reportType = 1 
 AND LEN(descriptionCode) IN (1, 4)

 UNION ALL -- better use this. (you can use UNION as well)

 SELECT * 
 FROM tblReportDescription
 WHERE reportType = 2 
 AND (LEN(descriptionCode) < 3 OR LEN(descriptionCode) > 5)

Result

Upvotes: 1

Dale K
Dale K

Reputation: 27201

You need additional brackets to ensure the OR gets evaluated correctly

SELECT *
FROM tblReportDescription
WHERE (
    reportType = 1 AND /* --> Additional bracket */ (LEN(descriptionCode) = 4 OR LEN(descriptionCode) = 1) /* <-- Additional bracket */ 
)
OR (
    reportType = 2 AND /* --> Additional bracket */ (LEN(descriptionCode) < 3 OR LEN(descriptionCode) > 5) /* <-- Additional bracket */
);

Because:

When more than one logical operator is used in a statement, the AND operators are evaluated first. You can change the order of evaluation by using parentheses.

Reference: Logical Operators

Although as Dai points out the logic can be simplified to avoid those ORs (below). But its still good to understand how AND/OR logic works:

SELECT *
FROM tblReportDescription
WHERE (
    reportType = 1 AND LEN(descriptionCode) IN (4,1)
)
OR (
    reportType = 2 AND LEN(descriptionCode) NOT IN (3,4,5)
);

FYI your original query is equivalent to the following, where only one of the 4 conditions needs to be true:

SELECT *
FROM tblReportDescription
WHERE (reportType = 1 and LEN(descriptionCode) = 4)
OR LEN(descriptionCode) = 1
OR (reportType = 2 and LEN(descriptionCode) < 3)
OR LEN(descriptionCode) > 5;

Upvotes: 2

Alexander Trakhimenok
Alexander Trakhimenok

Reputation: 6268

There is different precedence for AND/OR operators.

https://learn.microsoft.com/en-us/sql/t-sql/language-elements/operator-precedence-transact-sql?view=sql-server-ver15

You should put brackets like this:

SELECT *
into tblReportDescription
FROM (values 
             (34, 'Report tile 1', '3223', 1),
             (21, 'Report tile 2', '2', 1),
             (3, 'Report Title 3', '344322', 2),
             (13, 'Report Title 4', '34', 2),
             (8, 'Report Title 5', '1511', 1),
            (9, 'Report Title 6', '15', 2),
            (19, 'Report Title 7', '4444', 2),
            (14, 'Report Title 8', '6662566', 1)
     ) v(id, description, descriptionCode, reportType)
SELECT * FROM tblReportDescription
WHERE reportType = 1 and (LEN(descriptionCode) = 4 OR LEN(descriptionCode) = 1)
OR reportType = 2 and (LEN(descriptionCode) < 3 OR LEN(descriptionCode) > 5)

Upvotes: 0

Related Questions