****** Object:  Table [dbo].[Results]    Script Date: 22/01/2008 18:01:09 ******/
CREATE TABLE [dbo].[Results] (
	[ResultId] [int] IDENTITY (1, 1) NOT NULL ,
	[TournamentId] [int] NOT NULL ,
	[UserId] [int] NOT NULL ,
	[Points] [int] NOT NULL ,
	[Position] [int] NOT NULL ,
	[Bonus1] [bit] NOT NULL ,
	[Bonus2] [bit] NOT NULL ,
	[Bonus3] [bit] NOT NULL ,
	[Created] [smalldatetime] NOT NULL ,
	[Updated] [smalldatetime] NULL 
) ON [PRIMARY]
GO

/****** Object:  Table [dbo].[Tournaments]    Script Date: 22/01/2008 18:01:09 ******/
CREATE TABLE [dbo].[Tournaments] (
	[TournamentId] [int] IDENTITY (1001, 1) NOT NULL ,
	[LeagueId] [int] NOT NULL ,
	[VenueId] [int] NOT NULL ,
	[Date] [smalldatetime] NULL ,
	[Status] [int] NOT NULL 
) ON [PRIMARY]
GO

/****** Object:  Table [dbo].[Venues]    Script Date: 22/01/2008 18:01:09 ******/
CREATE TABLE [dbo].[Venues] (
	[VenueId] [int] IDENTITY (1, 1) NOT NULL ,
	[Name] [varchar] (100) COLLATE Latin1_General_CI_AS NULL ,
	[RegionId] [int] NOT NULL ,
	[AddressId] [int] NULL ,
	[ImageFilename] [varchar] (50) COLLATE Latin1_General_CI_AS NULL ,
	[TournamentDay] [int] NOT NULL ,
	[TournamentTime] [char] (5) COLLATE Latin1_General_CI_AS NULL ,
	[Website] [varchar] (100) COLLATE Latin1_General_CI_AS NULL ,
	[Longitude] [varchar] (20) COLLATE Latin1_General_CI_AS NULL ,
	[Latitude] [varchar] (20) COLLATE Latin1_General_CI_AS NULL ,
	[Status] [int] NOT NULL ,
	[Agent] [int] NULL ,
	[SalesExecutive] [int] NULL ,
	[Created] [smalldatetime] NOT NULL ,
	[CreatedBy] [int] NULL ,
	[Updated] [smalldatetime] NULL ,
	[UpdatedBy] [int] NULL 
) ON [PRIMARY]
GO

SELECT     v.SalesExecutive, COUNT(t.TournamentId) AS Tournaments, COUNT(r.ResultId) AS Results
FROM         dbo.Tournaments t INNER JOIN
                      dbo.Results r ON t.TournamentId = r.TournamentId RIGHT OUTER JOIN
                      dbo.Venues v ON t.VenueId = v.VenueId
WHERE     (t.Status = 2) AND (v.Created BETWEEN CONVERT(DATETIME, '2007-12-01 00:00:00', 102) AND CONVERT(DATETIME, '2008-01-31 00:00:00', 102)) AND 
                      (t.[Date] BETWEEN CONVERT(DATETIME, '2007-12-01 00:00:00', 102) AND CONVERT(DATETIME, '2008-01-31 00:00:00', 102))
GROUP BY v.SalesExecutive
HAVING      (v.SalesExecutive <> - 1)

quote:

---

Originally posted by visakh16

Why do you think result to be wrong? You are taking an inner join with results so will always get both totals as equal. Try replacing INNER JOIN with LEFT OUTER then you will be considering tournaments without results also.

---