How to select all records from master table GROUPS, but only the appropriate one from detail table SESSIONS?

Older postings have led me to believe a LEFT JOIN subquery is needed for this. The query below seems to work, but only for the first record. Subsequent records return NULL values for session_start etc.

How to get session data for all group records?

SELECT g.group_id, g.group_name
  FROM groups g

LEFT JOIN (
   SELECT session_id,
          group_id,
          session_start,
          session_stop
     FROM sessions
    WHERE :current_time < session_stop
 ORDER BY :current_time BETWEEN session_start AND session_stop DESC,
          session_stop
    LIMIT 1 ) s
ON s.group_id = g.group_id

The rules for conditionally returning the single record from "sessions" are:

1. Return the session record where current_time is between session_ start and session_stop.

2. Else, if none such exists, return the first session record where current_time is less than session_start.

3. Sessions having current_time larger than session_stop must not be returned.

The two tables, "groups"and "sessions":

CREATE TABLE groups (
    group_id   INTEGER PRIMARY KEY AUTOINCREMENT,
    group_name VARCHAR UNIQUE ON CONFLICT IGNORE
);

CREATE TABLE sessions (
    session_id      INTEGER PRIMARY KEY AUTOINCREMENT,
    group_id        INTEGER REFERENCES groups (group_id) ON DELETE CASCADE,
    session_start   REAL,
    session_stop    REAL
);

groups table content:

group_id    group_name
1           Group 1
2           Group 2

sessions table content:

session_id  group_id    session_start   session_stop
1       1               2459603.875     2459604.2291666665
2       1               2459604.875     2459605.2291666665
3       1               2459605.875     2459606.2291666665
261     2               2459603.875     2459604.2291666665
262     2               2459604.875     2459605.2291666665
263     2               2459605.875     2459606.2291666665

CodePudding user response：

You can use ROW_NUMBER() window function to pick 1 row from sessions for each group_id:

SELECT g.*, s.*
FROM groups g
LEFT JOIN (
  SELECT *, ROW_NUMBER() OVER (
              PARTITION BY group_id
              ORDER BY julianday('now','localtime') BETWEEN session_start AND session_stop DESC,
                       session_start 
            ) rn
  FROM sessions
  WHERE julianday('now','localtime') <= session_stop
) s ON s.group_id = g.group_id AND s.rn = 1;