October 15, 2014 at 2:05 pm
Hi, I had something happen today that I'm having trouble making sense of and I'm hoping someone can clear it up. I have two queries shown below. The second one is almost identical to the first, except there is some additional filtering in the where clause involving a subquery. The strange thing is when I add additional the additional filtering to the where clause in the second query it returns more records.
I don't understand how I can add additional filtering in a where clause and get MORE records.
As always any tips greatly appreciated, thanks.
Query 1:
select
distinct
p.Patient as 'RH_Patient_ID',
p.Notification,
p.First_Name,
p.Last_Name,
p.Birth_Date,
p.City,
p.Region,
pra.Id as 'RH_Practice_ID',
pra.PracticeName
from ProviderPracticepatient ppp
join Patient p on p.Patient = ppp.Patient
join ProviderPractice pp on pp.Id = ppp.ProviderPracticeId
join Practice pra on pra.Id = pp.PracticeId
join patientidentifiermap pim on pim.PatientId = p.Patient
where pra.Id = xxx
and ppp.Relation_Status = 'Active'
and p.Notification = 0
Query 2:
select
distinct
p.Patient as 'RH_Patient_ID',
p.Notification,
p.First_Name,
p.Last_Name,
p.Birth_Date,
p.City,
p.Region,
pra.Id as 'RH_Practice_ID',
pra.PracticeName
from ProviderPracticepatient ppp
join Patient p on p.Patient = ppp.Patient
join ProviderPractice pp on pp.Id = ppp.ProviderPracticeId
join Practice pra on pra.Id = pp.PracticeId
join patientidentifiermap pim on pim.PatientId = p.Patient
where pra.Id = xx
and ppp.Relation_Status = 'Active'
and p.Notification = 0
--Subquery to filter Patient Affiliations
and p.Patient not in
(select
p.patient
from Affiliation aff
join Practice_Affiliation pa on pa.AffiliationId = aff.AffiliationId
join Practice pra on pra.Id = pa.PracticeId
join ProviderPractice pp on pp.PracticeId = pra.Id
join ProviderPracticePatient ppp on ppp.ProviderPracticeId = pp.Id
join Patient p on p.Patient = ppp.Patient
where aff.AffiliationId != xx)
October 15, 2014 at 2:41 pm
There is a difference in the pra.ID = xx(x) line. Safe to assume that's a typo?
Offhand I don't see any reason you should be returning more rows. I'd have to be onsite and playing with it to figure out what the data pattern is doing that's so odd.
I'd start, however, by dropping both lists to separate temp tables and figuring out what the outlier rows look like. Hopefully that will provide hints as to why you're seeing this result.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
October 15, 2014 at 3:17 pm
Hi Craig, thanks for getting back to me so quickly. Yes, you are correct the pra.id should match, that's a type-o.
October 16, 2014 at 4:22 am
I've got nothing either. You'd think, logically, adding additional AND logic would result in further filtering, not the addition of more data.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
October 16, 2014 at 5:12 am
It doesn't sound logical. Can you extract the additional records from the second query? Maybe if you take a look at them it will show you the logic behind why they are selected....
October 16, 2014 at 9:15 am
Thank you everyone who responded.
It turns out it was a false alarm, I mis-read the record count. There weren't more records after adding the additional filter.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply