CODEWITHSUNDEEP
androidsqlite
Spacehamster
Spacehamster

Reputation: 135

Sqlite select multiple rows into a single row

I need a query that takes selects a bunch of entries, and then with those entries selects a bunch of rows into a single row. A typical entry is shown below, along with what a typical result would need to look like.

table1
id
-------
3     

table2
id      value       table1
--------------------------
5       value1      3
6       value2      3

table3
id      value       table1
-------------------------
9       value3      3
10      value4      3

table4
id      table1
---------------
14      3
15      3

table5
id      value       table4
-------------------------------
19      value5      14
20      value6      14
21      value7      15


result
result1 result2     result3     result4
------------------------------------------------------
3       value1      value3      value5, value6; value7

Only the first result of Table 1 and Table 2 are needed. Table 2 can often be null, value3 would need to go into result2. At the moment I select each entry with a statment like below which only returns result1, and then for each entry I do seperate queries for each result

SELECT DISTINCT id FROM (
    -complicated select in here
) AS temp;

And then for each id separably execute:

SELECT value FROM table2 WHERE table1 = id LIMIT 1;

SELECT value FROM table3 WHERE table1 = id LIMIT 1;

SELECT value, table4.id FROM table4, table5 WHERE table4.id = table5.table4 AND table1 = id;

and use code to format it into something like the results table.

The problem is only the first query is done on a background thread, while the rest of them are done on the UI thread. I'd like to do it all in one query, but am having trouble table4 into a single row and only selecting the first result of table2 and table3.

Upvotes: 2

Views: 3274

Answers (1)

Chris Cooper
Chris Cooper

Reputation: 5112

For conditional relationships like this you will have a much easier time running multiple selections, or creating a temporary table, filling it with keys in stages and then doing a final selection against the temporary table.

Looking at result4, in effect you are attempting to denormalise and then renormalise the data in the table using outer joins with conditional logic, it is probably possible to do, but would run against the grain of 'wise' database development and you are likely to end up with a query that is all but impossible to debug if you run into problems.

I know that sqlite is meant to be a simple implementation of the SQL standard for using in lightweight environments, so I'm not sure how complex your SQL can be before it gets into trouble.

It's not entirely clear to me what you are trying to achieve, but a workaround might be using several UNION statements like this:

SELECT DISTINCT source, id FROM (
    SELECT table2.table1 AS table1, 'table2' AS source, value AS id FROM table2 
    UNION
    SELECT table3.table1, 'table3', value FROM table3 
    UNION
    SELECT table4.table1, 'table5', value FROM table5, table4 
    WHERE table4.id = table5.table4)
WHERE table1 = 3;

Then you would get result like this:

source    | id
-----------------------
table2    | value1
table3    | value3
table4    | value5
table4    | value6
table4    | value7

Your other option is to create a view with all the unions (and all three columns, not just the two selected above) and then you can just select the value for table1 to get the same result.

Upvotes: 3

Related Questions