Deno Agüero Deno Agüero - 4 months ago 24
Android Question

Sqlite - Multiple INNER JOIN on same Table with Limit

In my Android application I have a Sqlite Database with a Question Table with 6 topic-IDs. Now I want to get a certain number of questions of every topic. That means: 15 questions with topic-ID 1, 5 with topic-ID 2, 7 with topic-ID 3 and 4 and 3 with topic-ID 5 and 6.

I think I need a multipe inner join and the limit function but I don't know much about building queries like this.

Do you have any ideas?

Answer

A simple method is to use union all and limit:

(select q.* from questions q where q.topicid = 1 order by random() limit 15) union all
(select q.* from questions q where q.topicid = 2 order by random() limit 5) union all
(select q.* from questions q where q.topicid in (3, 4) order by random() limit 7) union all
(select q.* from questions q where q.topicid in (5, 6) order by random() limit 3) ;

I didn't realize that SQLite seems to have problems with subqueries and union all. In any case, this version seems to work:

with q1 as
    (select q.* from questions q where q.topicid = 1 order by random() limit 15),
     q2 as
    (select q.* from questions q where q.topicid = 2 order by random() limit 5),
    q34 as 
    (select q.* from questions q where q.topicid in (3, 4) order by random() limit 7),
    q56 as
    (select q.* from questions q where q.topicid in (5, 6) order by random() limit 3)
select * from q1 union all
select * from q2 union all
select * from q34 union all
select * from q56;