Build mini-league table of where everybody is at in the walks

Question

I have the following simple set of SQL Server tables for my 'Walks' DB:

CREATE TABLE [USERS] (
  [USERID] INT NOT NULL IDENTITY,
  [USERNAME] NVARCHAR(50),
  PRIMARY KEY ([USERID])
)

CREATE TABLE [GROUPS] (
  [GID] INT NOT NULL IDENTITY,
  [GNAME] NVARCHAR(50),
  PRIMARY KEY ([GID])
)

CREATE TABLE [GROUPWALKERS] (
  [GWID] INT NOT NULL IDENTITY,
  [GID] INT DEFAULT 0,
  [USERID] INT DEFAULT 0,
  PRIMARY KEY ([GWID])
)

CREATE TABLE [WALKS] (
  [WID] INT NOT NULL IDENTITY,
  [WNAME] NVARCHAR(50),
  [WORDER] INT DEFAULT 0,
  PRIMARY KEY ([WID])
)

CREATE TABLE [CURRENTSITU] (
  [CSID] INT NOT NULL IDENTITY,
  [USERID] INT DEFAULT 0,
  [WID] INT DEFAULT 0,
  [GID] INT DEFAULT 0,
  [STARTTIME] DATETIME,
  [ENDTIME] DATETIME,
  PRIMARY KEY ([CSID])
)

The idea behind the tables is that the USERS table stores the people who use my DB.

The GROUPS table stores the walking group names and the GROUPWALKERS table stores which users are in which group.

The WALKS table contains the walks and the order in which people should walk them.

The CURRENTSITU table keeps an on-going log of where each user is on the walks. It will contain a single row per user id/walk id, eg:

CSID   USERID     WID    GID   STARTTIME         ENDTIME
1      35         2      2     2021-05-16 09:15
2      36         1      2     2021-05-16 08:30
3      37         2      2     2021-05-16 08:00

There is a USERID 38 and 39 but they haven't started any walks yet

Some example inserts are:

SET IDENTITY_INSERT USERS ON
INSERT INTO USERS (USERID,USERNAME) VALUES (35,'Bill')
INSERT INTO USERS (USERID,USERNAME) VALUES (36,'Bob')
INSERT INTO USERS (USERID,USERNAME) VALUES (37,'Jill')
INSERT INTO USERS (USERID,USERNAME) VALUES (38,'Jean')
INSERT INTO USERS (USERID,USERNAME) VALUES (39,'Jack')
SET IDENTITY_INSERT USERS OFF

SET IDENTITY_INSERT GROUPS ON
INSERT INTO GROUPS (GID,GNAME) VALUES (1,'Group 1')
INSERT INTO GROUPS (GID,GNAME) VALUES (2,'Group 2')
SET IDENTITY_INSERT GROUPS OFF

SET IDENTITY_INSERT GROUPWALKERS ON
INSERT INTO GROUPWALKERS (GWID,GID,USERID) VALUES (1,2,35)
INSERT INTO GROUPWALKERS (GWID,GID,USERID) VALUES (2,2,36)
INSERT INTO GROUPWALKERS (GWID,GID,USERID) VALUES (3,2,37)
INSERT INTO GROUPWALKERS (GWID,GID,USERID) VALUES (4,2,38)
INSERT INTO GROUPWALKERS (GWID,GID,USERID) VALUES (5,2,39)
SET IDENTITY_INSERT GROUPWALKERS OFF

SET IDENTITY_INSERT WALKS ON
INSERT INTO WALKS (WID,WNAME,WORDER) VALUES (1,'Snowdon',0)
INSERT INTO WALKS (WID,WNAME,WORDER) VALUES (2,'Bluebell Wood',1)
INSERT INTO WALKS (WID,WNAME,WORDER) VALUES (3,'Mam Tor',2)
INSERT INTO WALKS (WID,WNAME,WORDER) VALUES (4,'River Seven',3)
INSERT INTO WALKS (WID,WNAME,WORDER) VALUES (5,'Scar Fell',4)
SET IDENTITY_INSERT WALKS OFF

SET IDENTITY_INSERT CURRENTSITU ON
INSERT INTO CURRENTSITU (CSID,USERID,WID,GID,STARTTIME,ENDTIME) VALUES (1,35,2,2,'2021-05-16 09:15',NULL)
INSERT INTO CURRENTSITU (CSID,USERID,WID,GID,STARTTIME,ENDTIME) VALUES (2,36,1,2,'2021-05-16 08:30',NULL)
INSERT INTO CURRENTSITU (CSID,USERID,WID,GID,STARTTIME,ENDTIME) VALUES (3,37,2,2,'2021-05-16 08:00',NULL)
SET IDENTITY_INSERT CURRENTSITU OFF

Now what I'm trying to do is a mini-league table of where everybody is at in the walks so I tried:

select g.GID, u.USERID, w.WORDER, cs.STARTTIME 
from GROUPS g
left join GROUPWALKERS gw on g.GID=gw.GID 
left join CURRENTSITU cs on cs.GID=g.GID 
left join WALKS w on cs.WID=w.WID 
inner join USERS u on gw.USERID=u.USERID  
where g.GID = 2 
order by w.WORDER, cs.STARTTIME, u.USERID

The above is try and grab the group id, user id, what walk they are on in the pre-defined order and the start time of this current walk. I'm then doing a simple 'order by' to create my table.

My problem is that even though I've tried moving round the joins and/or using different joins I just can't get:

GID    USERID    WORDER     STARTTIME  
2      37        2          2021-05-16 08:00
2      35        2          2021-05-16 09:15
2      36        1          2021-05-16 08:30
2      38        NULL       NULL
2      39        NULL       NULL

I get:

GID    USERID    WORDER     STARTTIME  
2      37        2          2021-05-16 08:00
2      35        2          2021-05-16 08:00
2      36        2          2021-05-16 08:00
2      38        2          2021-05-16 08:00
2      39        2          2021-05-16 08:00

or I get all of the users, but the users that have done other walks show the start time of their other walks. Only the users who have done no walks at all show up with a WORDER and STARTTIME of Null

Any ideas?

Thanks

user_1552749 · Answer

Column keys are a little confusing. Might want to name them more verbosely. Be mindful of which table you use as your base (FROM clause). Starting from Groups and GroupStats, you are effectively asking for those details first, then user info if available. A more straightforward approach is to think on what your query is hoping to accomplish, which is primarily a stat chart on users.

Being that's the desired data, I'd start with Users, then join their group membership, then group details, and then group activity and walk detail data as LEFT OUTER JOINs, being that you want to return their data if it exists, and NULL if it does not.

SELECT  g.GID
        ,u.USERID
        ,w.WORDER
        ,c.STARTTIME
FROM    dbo.USERS                       AS u
        INNER JOIN dbo.GROUPWALKERS     AS g ON g.USERID = u.USERID
        INNER JOIN dbo.GROUPS           AS g2 ON g2.GID = g.GID
        LEFT OUTER JOIN dbo.CURRENTSITU AS c ON c.USERID = u.USERID
                                                AND c.GID = g.GID
        LEFT OUTER JOIN dbo.WALKS       AS w ON w.WID = c.WID;

Build mini-league table of where everybody is at in the walks

1 Answers