Where Criteria - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Where Criteria

Author

Topic

sross81
Posting Yak Master

228 Posts

Posted - 2009-07-15 : 14:32:47

I have this hiv_ table that can have multiple entries for each person. For example:

person_id, assess_code,b.p_managed_elsewhere, b.create_timestamp
1,042,0,6/17/09
1,042,1,6/30/09

If there is ever an entry where assess_code is not '042' and
b.p_managed_elsewhere = 1 then I want them completely filtered out but for whatever reason my where criteria will still report it back if one of the entries does meet the criteria even if the rest don't. Can someone see if they see anything wrong with how its set up?

select a.first_name,a.last_name,a.date_of_birth,b.p_managed_elsewhere,
b.assesscode
from person a
join hiv_ b on a.person_id = b.person_id
where assesscode = '042' and
p_managed_elsewhere <> 1
group by a.first_name,a.last_name,a.date_of_birth,b.p_managed_elsewhere,
b.assesscode

Thanks in Advance!
Sherri

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-07-15 : 15:09:01

It doesn't look wrong.
Can you give example data and wanted output to make it more clear?

No, you're never too old to Yak'n'Roll if you're too young to die.

sross81
Posting Yak Master

228 Posts

Posted - 2009-07-15 : 16:05:34

So for example since person_id 1 has two entries and one of them does not meet the criteria of assess_code = '042' and p_managed_elsewhere = 1, but one of them does. I don't want person_id 1 to be returned at all because they don't always meet the criteria.

1,042,0,6/17/09 meets
1,042,1,6/30/09 does not meet

therefore since one entry for this person doesn't meet the criteria they should not be returned.

But since one entry does it seems to return the data. Am I making sense?

quote:
Originally posted by webfred

It doesn't look wrong.
Can you give example data and wanted output to make it more clear?

No, you're never too old to Yak'n'Roll if you're too young to die.

Thanks in Advance!
Sherri

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2009-07-15 : 16:32:10

maybe something like this?

select 
	a.first_name,
	a.last_name,
	a.date_of_birth,
	b.p_managed_elsewhere,
	b.assesscode
from 
	person a
join 
	hiv_ b 
	on a.person_id = b.person_id
left outer join
	(
		select distinct
			person_id
		from
			hiv_b
		where 
			assesscode = '042' OR 
			p_managed_elsewhere <> 1 	
	) t
	on b.person_id = t.person_id
WHERE
	t.person_id IS NULL
group by 
	a.first_name,
	a.last_name,
	a.date_of_birth,
	b.p_managed_elsewhere,
	b.assesscode

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources