Select 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

Transact-SQL (2000)

Select Query

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2004-09-17 : 08:29:27

Nikhil writes "I have one table with the following fields with the data:

PO No Amt Inv Amt Rec Category
1 1000 0 Inv
1 0 1000 Rec
2 1111 0 Inv
2 0 1111 Rec
3 2222 0 Inv
4 0 3333 Rec
5 5555 0 Inv

I want a query which will select PO without category as "Rev". The result of query is as follows:

PO No Amt Inv Amt Rec Category
3 2222 0 Inv
5 5555 0 Inv

I want the PO with categoryas "Inv" but without "Rev"

Thanks & Regards,
Nikhil"

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-09-17 : 08:48:29

maybe:

select *
from MyTable
where category <> 'Rec'

Go with the flow & have fun! Else fight the flow

drymchaser
Aged Yak Warrior

552 Posts

Posted - 2004-09-17 : 10:09:04

select * from myTable
where PO in (
select PO
from myTable
group by PO
having max(category) = 'Inv')

sahu74
Posting Yak Master

100 Posts

Posted - 2004-09-17 : 10:30:21

This should work. Give it a try.


Select * from TblName
where [PO No] NOT IN
   (SELECT [PO No] FROM TblName
    WHERE Category='Rec')

PKS.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources