Inner join removes some rows unnecessarily-CodePudding

I have 3 tables defined like so

CREATE TABLE participants(
    id SERIAL PRIMARY KEY,
    Name TEXT NOT NULL,
    Title TEXT NOT NULL
);

CREATE TABLE meetings (
    id SERIAL PRIMARY KEY,
    Subject TEXT NOT NULL,
    Organizer TEXT NOT NULL,
    StartTime TIMESTAMP NOT NULL,
    EndTime TIMESTAMP NOT NULL
);

CREATE TABLE meetings_participants(
    meeting_id int not null,
    participant_id int not null,
    primary key (meeting_id, participant_id),
    foreign key(meeting_id) references meetings(id),
    foreign key(participant_id) references participants(id)
);

I want to find meetings happening today with participants in them. When I run this query I basically get them

SELECT * from meetings 
INNER JOIN meetings_participants ON meetings.id = meetings_participants.meeting_id 
INNER JOIN participants ON meetings_participants.participant_id = participants.id 
WHERE starttime::date = NOW()::date;

Problem is this query discards meetings where there are no participants yet, I still wish to include them into my query result. How can I modify my query to work like that ?

CodePudding user response：

You need a LEFT JOIN instead of INNER. Using ::date casting you are implying that you are only interested them to be taking place today, whether or not it might already ended. Still you should include EndTime in your query, taking into consideration that there might be meetings that span over several days:

SELECT * from meetings 
left join meetings_participants on meetings.id = meetings_participants.meeting_id 
left join participants on meetings_participants.participant_id = participants.id 
WHERE starttime::date <= NOW()::date and endtime::date >= NOW()::date ;

DBFiddle demo here.

EDIT: Participants' name and title as JSON array:

SELECT id, subject, organizer, starttime, endtime, jsonb_pretty(tmp.participants)
from meetings m
         left join lateral (
    select jsonb_agg(row_to_json(tp)) as participants
    from (select p.name, p.title
          from meetings_participants mp
                   inner join participants p on mp.participant_id = p.id
          where mp.meeting_id = m.id
         ) tp
    ) tmp on true
WHERE starttime::date <= NOW()::date
  and endtime::date >= NOW()::date;

DBFiddle demo for participants added as JSON

CodePudding user response：

You did not mention whether you want each participant on a separate row or as an aggregate (e.g. a comma separated list). If former then change inner to left join. For the latter case you could:

SELECT meetings.*, (
    SELECT string_agg(participants.name, ', ')
    FROM meetings_participants
    JOIN participants ON meetings_participants.participant_id = participants.id
    WHERE meetings_participants.meeting_id = meetings.id
) AS participants_list
FROM meetings 
WHERE starttime::date = current_date