help on pick up only one state

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)

help on pick up only one state

Author

Topic

cr488
Starting Member

23 Posts

Posted - 2006-04-21 : 16:04:19

I am trying to only pick up one state for each ID in a table called state_type

There is three types in the table let's say A,B,C

I only want to pick up the state in type A if type A is valid, or type B state if type A is not valid...

State_type

State_id type state
1 A CA
1 B FL
1 C CA
2 A CA
3 B GA
3 C IA

Select state
from state_type
where type = 'A' or (type <> 'A' and type = 'B')

I always get both type A and type B state in the result for id 1, can you help me on this? Thanks a lot!!

KenW
Constraint Violating Yak Guru

391 Posts

Posted - 2006-04-21 : 16:11:24

What decides whether or not Type A is valid?

cr488
Starting Member

23 Posts

Posted - 2006-04-21 : 16:15:08

If there is a type A in the type column, I will pick up that state, if there is no type A in the ype column, I will pick up the state for type B....

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-04-21 : 17:20:34


select	top 1
	state
from
	state_type
where
	State_id = 1
order by
	type

CODO ERGO SUM

cr488
Starting Member

23 Posts

Posted - 2006-04-21 : 18:27:55

Thanks for reply. But I am not going to only get id 1, I am going to have more 10000 id to pick up and join to other tables. This is just a simple example to show what I want to do. Please advice.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2006-04-21 : 19:03:18

[code]
select t1.*
from state_type t1
join (select state_id, min(type) as type
from state_type
group by state_id) t2 on t1.state_id = t2.state_id and t1.type = t2.type
[/code]

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

Blog thingie: [URL="http://weblogs.sqlteam.com/mladenp"]

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-04-21 : 20:51:46

quote:
Originally posted by cr488
...Please advice.

Next time, state that actual problem you are trying to solve, instead of some simple example that has nothing to do with what you want. We can't read your mind.

CODO ERGO SUM

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources