Search code examples
androidsqliteandroid-sqliteandroid-room

Android Room: Query not returning all rows with a join

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.

Solution

  • 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.