Conditional select base on the same column

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)

Conditional select base on the same column

Author

Topic

zhengsl
Starting Member

2 Posts

Posted - 2006-11-22 : 02:00:46

Hi

I have problem with selecting the following

Table : Test
Columns : IC, Reason(possible values 1 or 2)

I would like to select IC with Reason value 1 and if the IC with Reason value 1 does not exist then choose IC with Reason value 2.

Data
IC Reason
123 1
123 2
234 2

Expected result
IC Reason
123 1
234 2

Kindly advise!!
Thanks in advance
Charlie Tey

PSamsig
Constraint Violating Yak Guru

384 Posts

Posted - 2006-11-22 : 02:12:21

[code]SELECT IC, MIN(Reason)
FROM Test
GROUP BY IC[/code]

-- Alice came to a fork in the road. "Which road do I take?" she asked. "Where do you want to go?" responded the Cheshire cat. "I don't know," Alice answered. "Then," said the cat, "it doesn't matter."

zhengsl
Starting Member

2 Posts

Posted - 2006-11-22 : 03:35:09

Thanks for the quick reply! Sorry I forgot to mention I also have many other columns joining many other tables. How??

Table : Test
Columns : IC, Reason(possible values 1 or 2), Gender, Appointment

Table : Data
Columns : IC, Race, Religion

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-22 : 03:38:46

select test.ic, test.reason, test.gender, test.appointment, data.race, data.religion
from test
inner join (SELECT IC, MIN(Reason) mr FROM Test GROUP BY IC) q on q.ic = test.ic and q.mr = test.reason
inner join data on data.ic = q.ic

Peter Larsson
Helsingborg, Sweden

PSamsig
Constraint Violating Yak Guru

384 Posts

Posted - 2006-11-22 : 12:03:17

Hmmm, so Race and Religion are set once, but Gender can change from appointment to appointment ... those Yankies are strange ...

-- Alice came to a fork in the road. "Which road do I take?" she asked. "Where do you want to go?" responded the Cheshire cat. "I don't know," Alice answered. "Then," said the cat, "it doesn't matter."

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-11-22 : 12:08:21

Maybe the system is for sex change operations ? That explain the Gender column in appointment table

PSamsig
Constraint Violating Yak Guru

384 Posts

Posted - 2006-11-22 : 12:21:48

Yeah, my thought too, really strong in thier belives but switch orientation as they seem fit?

-- Alice came to a fork in the road. "Which road do I take?" she asked. "Where do you want to go?" responded the Cheshire cat. "I don't know," Alice answered. "Then," said the cat, "it doesn't matter."

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources