don't understand the logic

Started by mikev1976, Feb 23 2012 05:47 PM

You cannot reply to this topic
Go to first unread post

2 replies to this topic

#1 mikev1976

Newbie

Members
26 posts

Gender:Male
Location:37066

Posted 23 February 2012 - 05:47 PM

This is my query:

select EligibilityFirstName,EligibilityLastName,EligibilityDateOfBirth,ExternalUserId, u.NameFirst,u.NameLast,DateOfBirth
from Member.Users as u
join Outreach_Core.CallAppointments as ca on u.UserId=ca.CalleeId
where u.SponsorId='AC475310-87AC-471B-90F6-9F5600B3AD3B'
and exists
--check to see if call was made in the last week
(select CallDate from Outreach_Core.Calls as c where c.CallDate >= @startDate and c.CallDate < @endDate and ca.callappointmentid=c.CallAppointmentId)
and(
exists
(select * from Outreach_Core.Calls as c
where ca.callappointmentid=c.CALLAPPOINTMENTID and c.CallResultTypeId=4)
and ca.CallAppointmentStatusTypeId=3
)
order by u.NameLast

So in my result set I get person that has the attached call dates. The way I understand the query they should not show up. They meet the conditions inside of the second exists statement but that is joined by an and to filter the call dates. The call dates for this user do not match the call date filter so why are they showing up?

Attached File(s)

CallResults.PNG (4.88K)
Number of downloads: 4

Back to top

#2 justsomeguy

More Human Than Human

Moderator
24,331 posts

Gender:Male
Location:Phoenix
Languages:Focusing on PHP and JavaScript

Posted 23 February 2012 - 05:59 PM

The exists clause is true if the query returns any results at all, so that query is returning at least one record.

Know your history: Babbage | Lovelace | Turing | Hopper | Ritchie
ConTEXT Sublime Text Opera PHP MySQL phpMyAdmin
Use a debugger: Firefox, IE, Chrome, Safari, or Opera
Know the foundations of computer science: algorithms, machine architectures, data structures, etc. Don't just blindly copy techniques from application to application. Know what you are doing, that it works, and why it works. Don't think you know what the industry will be in five years time or what you'll be doing then, so gather a portfolio of general and useful skills. Try to write better, more principled code. Work to make "programming" more of a professional activity and less of a low-level "hacking" activity (programming is also a craft, but not just a craft). Learn from the classics in the field and the better advanced textbooks; don't be satisfied with the easily digested "how to" guides and online documentation - it's shallow.
-- Bjarne Stroustrup

He that teaches himself has a fool for a master.
-- Benjamin Franklin (paraphrased)