Picking up rows based on Role Code

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)

Picking up rows based on Role Code

Author

Topic

rkumar28
Starting Member

49 Posts

Posted - 2006-01-13 : 18:27:57

Hi,
Is there are to pick a row from table. My table has quite a few rows something like below.

policy…………..cust_cd…….Name
P11234………20…………King
P11234………10………….Ron
P11234……….00…………Bon

P22333……….10…………Joyce
P22333……….01…………Tom

I am trying to pick a Name for a policy that corresponds
to cust_cd = 20. If for any given policy cust_cd = 20 is not there, I need to pick up a row with cust_cd = 10.

I am trying to get this output below:

Policy…………..Cust_cd…….Name
P11234…………20…………King
P22333……….10….………..Joyce

Will appreciate any advice in this regards.

Thanks

Raj

rkumar28
Starting Member

49 Posts

Posted - 2006-01-13 : 20:44:14

Will appreciate any advice on this....

Thanks

Raj

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-01-13 : 21:44:26

[code]select *
from policy a
where a.cust_cd = '20'
union all
select *
from policy b
where b.cust_cd = '10'
and not exists (select * from policy x where x.cust_cd = '20' and x.policy = b.policy)[/code]

-----------------
'KH'
if you can't beat them, have someone else to beat them

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources