SQL Server NOT IN and LIKE using together

user_19307755 0
Viewed 72

I have a sample dbfiddle here and my goal is to filter out the emails from the CUS table's email column where CMT table does not have a record for cmt = 3 and CUS table's email field LIKE 'Ack%'.

Ex : According to the sample data set the expected result should be only Ack:email3@email.com, since order_no = 186350 and 186351 are the ones that does not have a Comment 3 in the cmt field also has Ack in the beginning of the email field on CUS table.

What I have tried but was not successful:

SELECT U.email
FROM CUS U inner join CMT M ON U.order_no = M.ord_no
WHERE M.cmt like 'Ack%'
AND U.order_no NOT IN (
    SELECT L.ord_no
    FROM CMT L
    WHERE L.cmt_sql_no = 3
);

Appreciate your help!

Sample data set:

CREATE TABLE CMT (
  id INT,
  ord_type char(1),
  ord_no char(8),
  line_sql_no smallint,
  cmt_sql_no smallint,
  cmt nvarchar(4000)
);

INSERT INTO CMT (id,ord_type,ord_no,line_sql_no,cmt_sql_no,cmt) VALUES (1,'O','186349',0,1,'Comment 1');
INSERT INTO CMT (id,ord_type,ord_no,line_sql_no,cmt_sql_no,cmt) VALUES (2,'O','186349',0,2,'Comment 2');
INSERT INTO CMT (id,ord_type,ord_no,line_sql_no,cmt_sql_no,cmt) VALUES (3,'O','186349',0,3,'Comment 3');

INSERT INTO CMT (id,ord_type,ord_no,line_sql_no,cmt_sql_no,cmt) VALUES (4,'O','186350',0,1,'Comment 1');
INSERT INTO CMT (id,ord_type,ord_no,line_sql_no,cmt_sql_no,cmt) VALUES (5,'O','186350',0,2,'Comment 2');

INSERT INTO CMT (id,ord_type,ord_no,line_sql_no,cmt_sql_no,cmt) VALUES (6,'O','186351',0,1,'Comment 1');
INSERT INTO CMT (id,ord_type,ord_no,line_sql_no,cmt_sql_no,cmt) VALUES (7,'O','186351',0,2,'Comment 2');

INSERT INTO CMT (id,ord_type,ord_no,line_sql_no,cmt_sql_no,cmt) VALUES (8,'O','186352',0,1,'Comment 1');
INSERT INTO CMT (id,ord_type,ord_no,line_sql_no,cmt_sql_no,cmt) VALUES (9,'O','186352',0,2,'Comment 2');
INSERT INTO CMT (id,ord_type,ord_no,line_sql_no,cmt_sql_no,cmt) VALUES (10,'O','186352',0,3,'Comment 3');
SELECT * FROM CMT

CREATE TABLE CUS (
  id INT,
  ord_type char(1),
  order_no nchar(10),
  status char(1),
  email nchar(50)
);

INSERT INTO CUS (id,ord_type,order_no,status,email) VALUES (1,'O','186349','4','Ack:email1@email.com');
INSERT INTO CUS (id,ord_type,order_no,status,email) VALUES (2,'O','186350','4','Inv:email2@email.com');
INSERT INTO CUS (id,ord_type,order_no,status,email) VALUES (3,'O','186351','4','Ack:email3@email.com');
INSERT INTO CUS (id,ord_type,order_no,status,email) VALUES (4,'O','186352','1','Inv:email4@email.com');

SELECT * FROM CUS
sql-server sql
1 Answers

This would appear to be ideally resolved using a not exists correlation, the following produces your desired results from the sample data set:

select c.email
from customer_comment c
where email like 'Ack:%'
and not exists (
  select * from OELINCMT_SQL o
  where o.ord_no = c.order_no 
    and o.cmt_sql_no = 3
);

DB<>Fiddle

user_153326500
Related