Help with this query - 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 2000 Forums

SQL Server Development (2000)

Help with this query

Author

Topic

someebody
Starting Member

2 Posts

Posted - 2011-03-08 : 21:15:08

Hello I need help with this query in sql server 2000, this is the table:

IdOrder Status User
1 Rcvd aa
1 ProcOk aa
1 Send aa
2 Rcvd bb
2 Fail bb
3 Rcvd aa
3 ProcOk aa
3 Send aa
4 Rcvd aa
4 Fail aa

And I need to show all the IdOrders where exists Status = 'Fail', it should be something like this:

IdOrder Status User
2 Rcvd bb
2 Fail bb
4 Rcvd aa
4 Fail aa

Thanks in advance

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-03-08 : 21:18:59

[code]
select *
from yourtable t
where exists
(
select *
from yourtable x
where x.IdOrder = x.IdOrder
and x.Status = 'Fail'
)[/code]

KH
[spoiler]Time is always against us[/spoiler]

someebody
Starting Member

2 Posts

Posted - 2011-03-08 : 21:44:15

Thank you so much! khtan

SELECT IdOrder, Status
FROM TData AS x
WHERE EXISTS
(SELECT IdOrder, Status, [User]
FROM TData AS y
WHERE (x.IdOrder = IdOrder) AND (Status = 'Fail'))

Done.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-03-08 : 21:49:09

you are welcome

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources