single vs multiple items - 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

SQL Server Development (2000)

single vs multiple items

Author

Topic

kashyap81
Starting Member

9 Posts

Posted - 2008-06-07 : 06:47:04

lets say we have two tables:
ItemTable
ItemID Name
1 Apple
2 Ball
3 Cat

ItemTypeTable
ItemID Type
1 1
2 1
2 2
3 2

I want a select statement that returns something along these lines:
ItemID Type
1 1
2 'Various'
3 2

basically if an Item has a single corresponding Type in the ItemTypeTable then it should return the Type, otherwise it should return 'Various' (or null or zero)

DiaTree.com
D.I.P. Pvt Ltd

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-06-07 : 10:24:28

select itemid,
case when min(type) < max(type) then 'Various'
ELSE CAST(min(type) AS varchar(12))
end as type
from table1
group by itemid
order by itemid

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources