Multiple conditions for AND and OR in WHERE clause SQL Server

Question

I am working in Microsoft SQL Server Management Studio. I have multiple tables I'm working with and multiple conditions (nearly 80) that I am querying on where I am trying to achieve a subset of joined data.

The idea is that I need to grab data from a join, where that data is not in a different join based on a condition (i.e., NOT IN), then I need to filter on a bunch of conditions involving multiple columns and multiple conditions on each column. My problem (I think) is arising from the OR condition. I have a column, say col5, where if it doesn't satisfy any of the conditions in the AND condition, then I need to filter further on it (hence, the OR condition).

What I have written so far is the following,

SELECT 
    handfulOfColumns
FROM 
    table1 AS t1
LEFT JOIN
    (SELECT *
     FROM table2
     WHERE col2 = 'thing0' or col2 = 'thing1') AS t2 ON t1.col1 = t2.col1
WHERE 
    t1.col1 NOT IN (SELECT t1.col1
                    FROM table1 t1
                    LEFT JOIN table3 t3 ON t1.col1 = t3.col1
                    WHERE col3 LIKE '%thing3%')
    AND (col4 > '#' AND col5 NOT LIKE 'thing4' 
         AND col5 != 'thing5' AND col5 NOT LIKE 'thing6' 
         AND col6 NOT LIKE 'thing7'
         --...
         --... add like 20 lines of conditions similar to above
         --...
         AND col34 NOT LIKE 'thing77' AND LEN(col35) > '#')
     OR (col5 NOT LIKE '% %' AND col5 LIKE '%[a-z]%' 
         AND col5 NOT LIKE '%[0-9]%' 
         AND col5 NOT LIKE 'thing209' 
         AND col5 NOT LIKE 'thing210');

The reasoning for this is I have a lot of (messy) phone numbers I am dealing with in col5, and overall there is a lot of cleaning to be done on all the columns. Some of these phone numbers will have text entered instead, and depending on the context I may want to keep them or filter them out (i.e. cases where the phone number is entered as 'business name', or 'business', or 'alskfjalsdkjf', rather than '555-5555' or '555 5555' etc). If the data meets the criteria of the AND conditions (mainly filtering out variations of col5 not like '%555-5555%'), then I need to further check and filter for any non-numeric characters in col5 through the OR condition.

There are no error messages - instead the query takes a really long time to finish and gives me more rows of data than I started with which is the opposite of what should be happening. Based on the structure of table1 and table2, there is potential to introduce duplicated rows by joining the two tables, but the amount of conditioning and sub-setting I am doing should give me far less than the original number of rows even with a few duplicates here and there.

I am not sure where my logic is failing for this, any help is appreciated :)

Answer 1

Other than than proper syntax corrections marc_s suggested, this actually runs just fine. Turns out I had a couple condition statements incorrect in the AND condition (i.e. a few = where logically they should have been != and like where they should have been not like) but the overall structure and logic is sound - reran on a couple test databases and it's working perfect. Apologies if this gave anyone a headache :)

Answer 2

Your logic looks quite messy, even if it provides the correct results. In order to make queries more readable, I would try the following:

1) If you are allowed to add some columns to existing schema, you can add some meaningful columns that can be reused in queries. Your table could look like this:

create table table1 
(
    Id INT NOT NULL,
    col1 VARCHAR(64),
    col2 VARCHAR(64),
    col3 VARCHAR(64),
    col4 VARCHAR(64),
    col5 VARCHAR(64),

    Thing4Like AS (CASE WHEN col4 > '#' AND col5 NOT LIKE 'thing4' THEN CAST(1 AS BIT) ELSE CAST(0 AS BIT) END) PERSISTED,
    Thing56 AS (CASE WHEN col5 != 'thing5' AND col5 NOT LIKE 'thing6' THEN CAST(1 AS BIT) ELSE CAST(0 AS BIT) END) PERSISTED
    -- other columns may come here
)

This allows you to write much more readable queries and even make the queries faster, as some information is precomputed. Also, persisted columns allow indexes.

2) If you cannot alter the schema, you can create a view over table1 containing extra logic as presented above. For extra performance, they can be indexed.