Problem with a WHERE NOT EXISTS clause

Transact-SQL
1

Hi everyone

I'm having problems retrieving the correct data using a WHERE NOT EXISTS clause and wonder if anyone could please help me. The script below is supposed to look for anyone under the age of 25 who doesn't not have a mailing type like '%25%' and then put them into the temporary table TEMPADDU25B.

This works fine if the database record doesn't have ANY mailing types at all. However, if someone doesn't have a mailing type like '%25%' but does have another mailing type of, say, like '%30' then the contact will not be retrieved. I hope this makes sense!

Ideally I want to retrieve ALL contacts that DON'T have a mailingtype like '%25%' whether or not they have any other mailtype on their record.

Many thanks
Jon

select distinct contact.serialnumber, contact.dateofbirth, mailingpreference.mailingtype,
GETDATE() AS [todays date], [current age] INTO TEMPADDU25B
from contact
left join mailingpreference on contact.serialnumber=mailingpreference.serialnumber
CROSS APPLY(VALUES (DATEDIFF(YY,dateofbirth,GETDATE()) - CASE WHEN DATEADD(YY,DATEDIFF(YY,dateofbirth,GETDATE()),dateofbirth) > GETDATE() THEN 1
ELSE 0
END)) A([current age])
where NOT EXISTS (SELECT mailingtype from mailingpreference WHERE mailingtype LIKE '%25%') and [current age] <25 OR
mailingpreference.mailingtype IS NULL and [current age] <25
ORDER BY SERIALNUMBER

2

hi

i took a look at it

hope this helps
:slight_smile:
:slight_smile:

drop create data ... 
use tempdb 
go 

drop table contact
go 

drop table mailingpreference
go 

create table contact
(
serialnumber int ,
dateofbirth date 
)
go 

create table mailingpreference
(
serialnumber int ,
mailingtype varchar(10) null
)
go

insert into contact select 1,'1991-01-10'
insert into contact select 2,'2001-06-15'
go 
select * from contact 
go 

insert into mailingpreference  select 1,'25'
go 
select * from mailingpreference 
go
SQL ... 
;WITH cte 
     AS (SELECT DISTINCT contact.serialnumber, 
                         contact.dateofbirth, 
                         mailingpreference.mailingtype, 
                         Getdate()                                            AS 
                            [todays date], 
                         ( Datediff(yy, contact.dateofbirth, Getdate()) - 1 ) AS 
                            current_age 
         FROM   contact 
                LEFT JOIN mailingpreference 
                       ON contact.serialnumber = mailingpreference.serialnumber) 
SELECT * 
FROM   cte a 
       LEFT JOIN mailingpreference b 
              ON ( a.current_age < 25 ) 
                 AND ( b.mailingtype LIKE '%25%' 
                        OR b.mailingtype IS NULL ) 
WHERE  b.serialnumber IS NULL

i am thinking this shorter SQL will also work

Shorter SQL .. 
SELECT DISTINCT contact.serialnumber, 
                contact.dateofbirth, 
                mailingpreference.mailingtype, 
                Getdate() AS [todays date], 
                Datediff(yy, contact.dateofbirth, Getdate()) - 1, 
                Cast(mailingpreference.mailingtype AS INT) 
FROM   contact 
       JOIN mailingpreference 
         ON contact.serialnumber = mailingpreference.serialnumber
Results

image

3

Hi there

Many thanks for your help with this :slight_smile:

Unfortunately I can't drop the CONTACT or MAILINGPREFERENCE tables as they are two of the main tables in the database which contains 1000s of records that are not directly related to this particular issue with the ages.

What I'm aiming to do is pull out a list of all contacts from the CONTACT table who do not have a MAILINGTYPE of '%25%' in the MAILINGPREFERENCES table. My original SQL works OK as long as there is also no other MAILTYPE in the table, but won't retrieve the records if another one exists.

I hope that I've explained this OK :slight_smile:

Best wishes
Jon

4

by another one exists
... do you mean duplicates ...

or for the same serial number different values
serial number mailingtype
1 25
1 35

-- something like this .. OKAY ???
SELECT *
FROM contacts a
JOIN mailingpreferences b
ON a.serialnumber = b.serialnumber
WHERE b.mailingtype NOT LIKE '%25%'

1 Like
5

Thank you - that has fixed it :slight_smile: