Tricky Joins & where clauses - got me stumped

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

Transact-SQL (2000)

Tricky Joins & where clauses - got me stumped

Author

Topic

Major Disorder
Starting Member

2 Posts

Posted - 2003-10-20 : 09:32:49

Hi,
After a week of trying everything I know I've resorted to asking you guys for some help. I've got 4 tables (quite a few columns ommited for brevity)

1 Users - Name & Id
2 Fixture - Fixture Name, Date , League , CreatedBy & Game Type
3 Point - Points & Id
4 FixtureRace - Records the Result of a Fixture , Race , User & Points scored

What I'd like in my query is

1) For each User, the count of entries in the FixtureRaces table for the current month only (showing 0 for no entries) and a sum of the points won (showing 0 for no points)
2) This list should be filtered (where'd) by the LeagueId + Date in the Fixture table.

I've tried the following query (and numerous variations too):

select u.Name , count(fr.RaceId), sum(p.points)
from users u
left join FixtureRace fr on fr.userid= u.userid
left join Point p on p.pointid = fr.pointid
full join Fixture f on F.FixtureId = fr.FixtureId
where DatePart(month,f.[Date]) = DatePart(month, Getdate())
and
f.LeagueId = @LeagueId
group by u.Name

The problem is that no matter what I've tried I cannot get a full list of users to show with "0" against them......probably really simple for you guys but it's really got me foxed.

Hope you can help

thanks

Pete

The create scripts for the tables are attached below:

if not exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Fixture]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
CREATE TABLE [dbo].[Fixture] (
[FixtureId] [int] IDENTITY (1, 1) NOT NULL ,
[GameId] [int] NOT NULL ,
[LeagueId] [int] NOT NULL ,
[Date] [datetime] NOT NULL CONSTRAINT [DF_Fixture_Date] DEFAULT (getdate()),
[CreatedBy] [int] NULL ,
CONSTRAINT [PK_Fixture] PRIMARY KEY CLUSTERED
(
[FixtureId]
) ON [PRIMARY]
) ON [PRIMARY]
END

GO

if not exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Point]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
CREATE TABLE [dbo].[Point] (
[PointId] [int] IDENTITY (1, 1) NOT NULL ,
[Points] [int] NULL ,
CONSTRAINT [PK_Point] PRIMARY KEY CLUSTERED
(
[PointId]
) ON [PRIMARY]
) ON [PRIMARY]
END

GO

if not exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Users]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
CREATE TABLE [dbo].[Users] (
[UserId] [int] IDENTITY (1, 1) NOT NULL ,
[Name] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
CONSTRAINT [PK_Users] PRIMARY KEY CLUSTERED
(
[UserId]
) ON [PRIMARY]
) ON [PRIMARY]
END

GO

if not exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FixtureRace]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
CREATE TABLE [dbo].[FixtureRace] (
[FixtureId] [int] NOT NULL ,
[RaceId] [int] NOT NULL ,
[UserId] [int] NOT NULL ,
[PointId] [int] NOT NULL ,
CONSTRAINT [PK_FixtureRace] PRIMARY KEY CLUSTERED
(
[FixtureId],
[RaceId],
[UserId]
) ON [PRIMARY] ,
CONSTRAINT [FK_FixtureRace_Fixture] FOREIGN KEY
(
[FixtureId]
) REFERENCES [dbo].[Fixture] (
[FixtureId]
),
CONSTRAINT [FK_FixtureRace_Point] FOREIGN KEY
(
[PointId]
) REFERENCES [dbo].[Point] (
[PointId]
),
CONSTRAINT [FK_FixtureRace_Users] FOREIGN KEY
(
[UserId]
) REFERENCES [dbo].[Users] (
[UserId]
)
) ON [PRIMARY]
END

GO

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-10-20 : 09:55:09

you are taking the wrong approach, break it down:

1) first, you want to start with all users. so,

SELECT userID from Users

2) now, you want to display the count of entries in the fixtures table per user for the current month:

SELECT userID, count(*) as FixtureCount
FROM Fixture
WHERE DatePart(month,f.[Date]) = DatePart(month, Getdate())
GROUP BY userID

3) and you also want total points per user:

SELECT UserID, sum(Points) as TotalPoints
FROM FixturePoints
GROUP BY USerID

Make sure all 3 queries work the way you want and return proper results. do it all in 3 parts. make sure you are happy. now join them together:


SELECT 
  UserID, ISNULL(FixtureCount,0) as FixtureCount, ISNULL(TotalPoints,0) as TotalPoint
FROM 
  Users
LEFT OUTER JOIN
  (SQL From part 2) A
ON A.UserID = Users.UserID
LEFT OUTER JOIN
  (SQL From part 3) B
ON
   B.UserID = Users.UserID

Notice the ISNULL() function replaces Nulls with 0. ALWAYS ALWAYS break down queries like this into the smallest part (in this case, everything should be GROUPED by userID) and then put them all together at the end.

- Jeff

Major Disorder
Starting Member

2 Posts

Posted - 2003-10-20 : 10:24:03

Thanks for that Jeff,
I can see I've still got a lot to learn SQL wise..... I've put the lot together for the full solution with a few other bits like the leagueId). To me this seems like quite a complex query, it's going to be on one of the higher hits pages on my site (i think), do you think I will have performance issues?

The FixtureRace table has around 1000 entries at the moment and I would guess will grow by about 500 / week when things hot up a bit. I don't have any plans to archive this data so I was wondering would it be sensible to normalise it into a separate "results" table in the medium to long term.

Thanks again for your swift reply, that one query had me stuck for weeks

SELECT
Users.[Name], ISNULL(FixtureCount,0) as FixtureCount, ISNULL(TotalPoints,0) as TotalPoint
FROM
Users
LEFT OUTER JOIN
(SELECT FR.userID, count(FR.FixtureId) as FixtureCount
FROM FixtureRace FR
join Fixture F on F.FixtureId = FR.FixtureId
WHERE DatePart(month,F.[Date]) = DatePart(month, Getdate())
AND F.LeagueId = 1
GROUP BY FR.userID) A
ON A.UserID = Users.UserID
LEFT OUTER JOIN
(SELECT UserID, sum(p.Points) as TotalPoints
FROM FixtureRace
join Point P on P.PointId = FixtureRace.PointId
GROUP BY USerID) B
ON B.UserID = Users.UserID

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources