I have 3 SQLite tables:
channel
channelId triad
1 1
2 1
3 0
video
channelId videoId topic
1 xx 1
1 yy 0
2 pp 0
3 kk 1
comment
commentId replyId videoId sentiment
NULL er41 xx -3
clxpo NULL kk 0.05
clutz NULL yy 2.38
NULL edg15 xx 1.7
clopq NULL pp 1
dkt2 NULL kk 0.95
For every channel where triad = 1 I need to know the sum of the sentiment from the channel videos where topic = 1 and the number of comments for that channel.
Keys are:
channel.channelId = video.channelId
video.videoId = comment.videoId
End result should be:
channelId sum(sentiment) count(number of comments) triad topic
1 -1,3 2 1 1
How can I chain that 3 tables to get the needed result?
It's a straightforward 3-way merge with some filtering to restrict it to just rows with triad and topic of 1, grouping on channelid:
SELECT ch.channelid
, sum(co.sentiment) AS sum_sentiment
, count(*) AS num_comments
FROM channel AS ch
JOIN video AS v ON ch.channelid = v.channelid
JOIN comment AS co ON v.videoid = co.videoid
WHERE ch.triad = 1 AND v.topic = 1
GROUP BY ch.channelid
ORDER BY ch.channelid;
which gives
channelid sum_sentiment num_comments
---------- ------------- ------------
1 -1.3 2
If you have a lot of rows in your tables, use the sqlite3 command line tool's .expert command to figure out what indexes should be added for an optimized execution plan.