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)  Simple Query . . . is there a better way?

Author  Topic  Jay99 468 Posts Posted - 2001-03-28 : 14:54:03 CREATE TABLE ContactMaster( intContactID INTEGER IDENTITY(100,1) NOT NULL, vcName VARCHAR(75) NOT NULL, ...blah,blah,blah... vcCategory VARCHAR(20), bIsActive BIT ) CREATE TABLE ContactCategory( vcCategory VARCHAR(20) NOT NULL ) GO ALTER TABLE ContactMaster ADD CONSTRAINT ContactMastervcCategoryFK FOREIGN KEY (vcCategory) REFERENCES ContactCategory(vcCategory) GO CREATE NONCLUSTERED INDEX vcCategoryIDX ON ContactMaster(vcCategory) GO I want to return a list of all available vcCategory from ContactCategory and the count of how many contacts are associated with that category. I have this: SELECT cc.vcCategory, (SELECT COUNT(*) FROM ContactMaster cm WHERE cm.vcCategory = cc.vcCategory) as 'intCount' FROM ContactCategory cc ORDER BY vcCategory ASC This works, but do you have a better idea? Thx Jay