selecting categories having 2diffent subcategories

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 2005 Forums

Transact-SQL (2005)

selecting categories having 2diffent subcategories

Author

Topic

sachin.hingole
Starting Member

15 Posts

Posted - 2009-05-05 : 10:19:38

Hi,

I would like to display the results only if its having two rows under same id in sql server 2005

eg. if CategoryID 1 is is for two different SubCategories like 1 and 2 then show 1 categoryID, If 1 Categoryid is for 1 and not for 2 then dont show 1 categoryid. ids are autogenerated numbers

Thanks in advance

Sachin Hingole

edit: moved to proper forum

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-05-05 : 10:41:05

if its about category id 1 and 2 only, then

select 
	distinct a.CategoryID
from
	yourtable a
where
	a.SubCategories=1 and exists(select * from yourtable where CategoryID=a.CategoryID and SubCategories=2)

If its just about the count of different sub-categories,

select 
	CategoryID,
	count(distinct SubCategories)
from
	yourtable
group by
	CategoryID
having 
	count(distinct SubCategories)>1

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources