SQLTeam.com | Weblogs | Forums

Select query not exists is not working even though there is a match


#1

i have a matching record in both tables : phone, email and arrivaldate, still the record coming as a result in the below query, if a record is a thre with the three matching values in both tables t1 and t2, i don't want to show that record as a result.
phone=7722036743, emailid: getrrdy@gmail.com, arrivaldate: 10/8/2015

 SELECT *
	FROM LodgingRequest t1
	WHERE NOT exists (SELECT * fROM PatientProfile t2 WHERE rtrim(t1.email) = rtrim(t2.LR_Email) and rtrim(t1.Phone) = rtrim(t2.LR_Phone) and CONVERT(varchar(10), t1.Arrivaldate, 101)= CONVERT(varchar(10), t2.LR_Arrivaldate, 101));

Please need a proper way to handle above query.

Thanks a lot for the helpful info.


#2

I don't see anything that would make the code not work, although you can and should get rid of the unneeded RTRIM() and CONVERT(). If the Arrivaldate columns are datetime, cast them to date rather than convert to varchar, which is vastly more overhead.

SELECT *
	FROM LodgingRequest t1
	WHERE NOT exists (SELECT * fROM PatientProfile t2 
        WHERE t1.email = t2.LR_Email and t1.Phone = t2.LR_Phone and 
            CAST(t1.Arrivaldate AS date)= CAST(t2.LR_Arrivaldate AS date) );

#3

Trailing spaces in the string?