Help with query on 1-to-many table

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 2000 Forums

SQL Server Development (2000)

Help with query on 1-to-many table

Author

Topic

footohi
Starting Member

10 Posts

Posted - 2006-05-22 : 16:28:34

I have a History table, and in my application I want to report on only records that have gone up to a specific process/status.
sample data in table:

ID# Status_Code
1 00
1 01
1 02
2 00
3 01

I need a result set for ID# 2 & 3
here's the logic:
select distinct ID# from mytable where (status_code='00' Or status='01') And (status_code<>'03')
i.e. if ID# has gone upto process/status 02 don't report

I can't believe I'm stumped on this one, but I trust someone will show me this isn't that difficult.

thanks,

footohi
Starting Member

10 Posts

Posted - 2006-05-22 : 17:36:29

I thought this would work, but it doesn't.

select HPR_ID from HPR_History
where not exists (Select HPR_ID from HPR_history where actn_status_code>'01')

Basically, I'm tried to do an unmatched query from the same table.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-05-22 : 17:56:10

SELECT HPR_ID, actn_status_code
FROM HPR_History
WHERE HPR_ID NOT IN (SELECT HPR_ID FROM HPR_History WHERE actn_status_code = '02')

Tara Kizer
aka tduggan

footohi
Starting Member

10 Posts

Posted - 2006-05-24 : 14:09:57

Perfect, Thanks!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources