Not getting expected results from SQLITE query

Question

I'm struggling to get the right result back from a LEFT JOIN with a WHERE clause with SQLITE. I have two tables - contents and actions.

I want a return of all actions where the content creation date is between a date A and date B. However, when I add the WHERE clause, i'm only getting the first action for each content item. When I remove the WHERE clause, I get all actions as expected.

Below is a simplified example of the tables:

Contents:

content_id	creation_date	content
1	2023-01-02	A
2	2022-12-11	B
3	2022-12-31	C
4	2023-01-07	D
5	2023-01-22	E

Actions:

action_id	Action	content_id
1	CLICK	2
2	CLICK	1
3	SEND	1
4	SEND	1
5	CLICK	3
6	SEND	4
7	SEND	4
8	CLICK	3
9	CLICK	5

When I run the following query:

SELECT 
c.content_id,
c.creation_date,
c.content,
a.action_id
a.action
FROM content c
LEFT JOIN actions a ON a.content_id = c.content_id
WHERE c.creation_date BETWEEN '2023-01-01' AND '2023-01-30'
GROUP BY a.action_id

I expected to see a result like:

content_id	creation_date	content.	action_id	action
1	2023-01-02	A	2	CLICK
1	2023-01-02	A	3	SEND
1	2023-01-02	A	4	SEND
4	2023-01-07	D	6	SEND
4	2023-01-07	D	7	SEND
5	2023-01-22	E	9	CLICK

The result I get is the first action for each content where creation date is between the range rather than each action associated with content created within the range. Am I missing something here?

Answer 1

For this sample data what you need is just an INNER join without any aggregation:

SELECT c.content_id, c.creation_date, c.content,
       a.action_id, a.action
FROM content c INNER JOIN actions a 
ON a.content_id = c.content_id
WHERE c.creation_date BETWEEN '2023-01-01' AND '2023-01-30';

See the demo.