SQL Server query where NOT EXISTS filter-CodePudding

I have two tables in my SQL Server which I'm trying to join and only get results by filtering using some conditions in where clause. First things first, I have created a sample fiddle here to have a look on my sample data of two tables.

Sample data fiddle

So here my goal is to combine both tables using order number columns and get only the email column in customer_comment table excluding the OELINCMT_SQL table record for lin_cmt_no = 3. So as an example let's say order number : 186349 in the fiddle's customer_comment table has 3 records in respective joined OELINCMT_SQL table. Specifically those are ID =1,2 and 3 which has cmt_sql_no = 1,2 and 3.So this record should not show in my query results since it has a record for cmt_sql_no = 3. Now if we take order number : 186350 it only has cmt_sql_no = 1 and 2. So this order number's email column should show as an result.

Now here's what I have tried :

SELECT C.email FROM customer_comment C INNER JOIN OELINCMT_SQL L ON LTRIM(RTRIM(C.order_no)) = LTRIM(RTRIM(L.ord_no))
WHERE NOT EXISTS (SELECT cmt_sql_no from OELINCMT_SQL  where cmt_sql_no = 3)

Basically I joined both tables on order_no = ord_no and then tried to connect a NOT EXIST there and added a sub query SELECT cmt_sql_no from OELINCMT_SQL where cmt_sql_no = 3. If the query works I should see [email protected] and [email protected] as the results. But I do not see anything. Which part am I doing wrong? And to get the expected results what should I edit here?

Appreciate your help!

CodePudding user response：

You should filter the order number in the NOT EXISTS clause as well. Otherwise, if there is at least one row in the whole table with cmt_sql_no=3, the result will be nothing.

SELECT 
    C.email 
FROM customer_comment C 
INNER JOIN OELINCMT_SQL L ON 
     LTRIM(RTRIM(C.order_no)) = LTRIM(RTRIM(L.ord_no))
WHERE NOT EXISTS (SELECT 1 from OELINCMT_SQL x 
                  where 
                       x.cmt_sql_no = 3 
                       and LTRIM(RTRIM(C.order_no)) = LTRIM(RTRIM(x.ord_no)))