Need help

Question

I have a table contain data like

PicklistNo Status

PKL019000 LOD

PKL019000 WIP

If any status contain Wip it give no result.

I used query :-

Select DISTINCT PickListNo,status from tablename WHERE PickListNo='PKL019000' and Status='LOD'

It gives me result. But I dont want any row if any row status contain WIP

Jignesh Trivedi · Answer

hi try following query Select DISTINCT PickListNostatus from #tempTable1 p WHERE PickListNo='PKL019000' and NOT EXISTS(select * from #tempTable1 where PickListNo = p.PickListNo and Status = 'WIP') hope this will help you.

Jignesh Trivedi · Answer

If you do not want to use DISTINCT than you can also group your query by picklistno and status if and only if status contain the only value LOD and WIP. :)

Ravi Shekhar · Answer

If i remove distinct then it will return 4 rows with lod status. But i dont want that.

srinivas thummalapalli · Answer

remove distinct in ur query then it will run fine.

Ravi Shekhar · Answer

Thanks for the query its working!!!