Select Count Join Tables

2 Mar 2006 9:35 PM

Jeff

DML Below:

I need a query that selects all of the tblsystem records (which will always
be unique) and gives me a count of how many tbldocument records that are tied
to each SystemName.

So the query would return the results:

Exchange, 3
Windows XP, 2
Windows 2003, 0

Thanks in advance!
Jeff

if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[tblDocument]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tblDocument]
GO

if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[tblSystem]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tblSystem]
GO

CREATE TABLE [dbo].[tblDocument] (
    [DocumentID] [int] IDENTITY (1000, 1) NOT NULL ,
    [Title] [varchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [CreationDate] [datetime] NOT NULL ,
    [SystemID] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[tblSystem] (
    [SystemID] [int] IDENTITY (10000, 1) NOT NULL ,
    [SubmitterID] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [SystemName] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[tblDocument] ADD
    CONSTRAINT [DF_tblDocument_CreationDate] DEFAULT (getdate()) FOR
[CreationDate],
    CONSTRAINT [PK_tblDocument] PRIMARY KEY CLUSTERED
    (
        [DocumentID]
    ) WITH FILLFACTOR = 90 ON [PRIMARY] ,
    CONSTRAINT [IX_tblDocument_Title] UNIQUE NONCLUSTERED
    (
        [Title]
    ) WITH FILLFACTOR = 90 ON [PRIMARY] ,
    CONSTRAINT [CK_tblDocument] CHECK ([title] is not null)
GO

ALTER TABLE [dbo].[tblSystem] ADD
    CONSTRAINT [PK_tblSystem] PRIMARY KEY CLUSTERED
    (
        [SystemID]
    ) ON [PRIMARY]
GO

insert into tbldocument    Values ('Small XP Title', '01/01/2006', 'Windows XP')
insert into tbldocument    Values ('Big XP Title', '02/01/2006', 'Windows XP')
insert into tbldocument    Values ('Another Exchange Title', '02/15/2006',
'Exchange')
insert into tbldocument    Values ('Exchange Mediaum Title', '02/15/2006',
'Exchange')
insert into tbldocument    Values ('Exchange Title', '02/15/2006', 'Exchange')

insert into tblsystem Values ('Joe Smith', 'Exchange')
insert into tblsystem Values ('Sally Ride', 'Windows XP')
insert into tblsystem Values ('Nate Black', 'Windows 2003')

2 Mar 2006 10:13 PM

Anith Sen

Do:

SELECT t1.SystemName, COUNT( t2.Title )
FROM tblsystem t1
LEFT OUTER JOIN tbldocument t2
ON t1.SystemName = t2.SystemID
GROUP BY t1.SystemName ;

--
Anith