Android Room: Query not returning all rows with a join

Question

I have two tables - table a and table b. In table a I have 20 columns, 10 of these are joined to table b. The reason for this is that the user can select multiple values which cant be stored into one cell/row/column in the table so it needs to be normalised out into its own table. Im having an issue when retriving the data. The query im using only returns results if data is present in both table a and b. But my use case allows the user to only have data in table a. I need to modify my query to return whatever data is present in the row regardless if there is no data in table b.

Here is condensed sinmplified version of my query (no need for all 20 columns)

SELECT
tableA.id,
tableA.currentSituation,
tableA.moodStart,
tableA.automaticThought1,
tableB.id,
tableB.allOrNothing,
tableB.blamingOthers,
tableB.catastrophizing,
FROM tableA
JOIN tableB
ON tableA.id = tableB.tableAid
ORDER BY tableA.currentSituation
DESC

Any help modifying this query to return all rows/data present regardless if no data being present in table b would be greatly appreciated.

Hi, how are you? in your code: do you include "SELECT" word? — Agustin Silva Albistur, Oct 05 '20 at 12:41

score 0 · Answer 1 · answered Oct 05 '20 at 13:26

0

how are you? i see two errors to correct your script: First of all Add "SELECT" word to your script and another change "JOIN" to "LEFT OUTER JOIN":

SELECT tableA.id,
tableA.currentSituation,
tableA.moodStart,
tableA.automaticThought1,
tableB.id,
tableB.allOrNothing,
tableB.blamingOthers,
tableB.catastrophizing,
FROM tableA
LEFT JOIN tableB
ON tableA.id = tableB.tableAid
ORDER BY tableA.currentSituation
DESC;

More info: sqlite joins reference

answered Oct 05 '20 at 13:26

Agustin Silva Albistur

157
7

java.lang.NullPointerException: Parameter specified as non-null is null: method kotlin.jvm.internal.Intrinsics.checkNotNullParameter, parameter id – AndroidDev123 Oct 05 '20 at 23:07
I get the above error if there is no data in table b – AndroidDev123 Oct 05 '20 at 23:07
i found this error reported here: https://stackoverflow.com/questions/47849219/java-lang-illegalargumentexception-parameter-specified-as-non-null-is-null-me maybe this error is more relative to your Parameters' Class definition: add ? to all suspecting variables types: val id: String? And try again. – Agustin Silva Albistur Oct 06 '20 at 15:08

score 0 · Accepted Answer · answered Oct 06 '20 at 00:02

The LEFT JOIN keyword returns all records from the left table [tableA], and the matched records from the right table [tableB]. The result is NULL from the right side, if there is no match.

from https://www.w3schools.com/sql/sql_join_left.asp

I think LEFT JOIN is what you want. But you'll need to check for null since a tableB entry might not exist.

Android Room: Query not returning all rows with a join

2 Answers2