Problems with sql query using rawQuery

Question

My problem is that the query that I put below, used in DbBrowser for Sqlite on my PC, returns the data correctly. But once executed in the application it does not show me all the data I am missing what results from the UNION SELECT, it is as if I only solved the first part before UNION

val parametro_lista_seleccionada: Array<String> =
        arrayOf(lista_seleccionada.toString()) //Creamos un array con el id de la lista seleccionada para pasarla como parametro al SELECT
val cursor_consulta_sql: Cursor = bd_conexion.rawQuery("SELECT * FROM contenido_lista AS cl, productos AS p WHERE cl.id_lista = ? AND cl.id_producto = p.id_producto UNION SELECT * FROM contenido_lista AS cl, productos_manuales AS pm WHERE cl.id_lista = ? AND cl.id_producto = pm.id_producto ORDER BY p.id_seccion,pm.id_seccion" ,parametro_lista_seleccionada)

Answer 1

I assume that both unioned queries return the same number of columns, since you don't get any errors.

You should use proper JOIN syntax with an ON clause and not this archaic syntax with the comma.

Also, the ORDER BY clause when used at the end of UNION, it is applied to the result of the UNION and not to any of the the participating queries.
So you can't use the aliases of the tables that you have previously set, to the columns after ORDER BY.

Finally, in your query there are 2 ? placeholders, so you must pass as the 2nd argument of rawQuery() an array of 2 strings. I guess it's the same string, but you must use it twice in the array.

So change to this:

val parametro_lista_seleccionada = arrayOf(lista_seleccionada.toString(), lista_seleccionada.toString())
val cursor_consulta_sql: Cursor = bd_conexion.rawQuery(
    "SELECT * " +
    "FROM contenido_lista AS cl INNER JOIN productos AS p " + 
    "ON cl.id_producto = p.id_producto " + 
    "WHERE cl.id_lista = ? " + 
    "UNION " + 
    "SELECT * " + 
    "FROM contenido_lista AS cl INNER JOIN productos_manuales AS pm " + 
    "ON cl.id_producto = pm.id_producto " + 
    "WHERE cl.id_lista = ? " + 
    "ORDER BY id_seccion" ,
    parametro_lista_seleccionada
)

Just by the name of lista_seleccionada, I will make a guess, maybe it is not a string. Is it a list?
If so then lista_seleccionada.toString() will not return 2 strings, but 1 string which is a comma separated list of the strings.
So change to:

val parametro_lista_seleccionada = arrayOf(lista_seleccionada[0], lista_seleccionada[1])

or if the items of the list are numbers:

val parametro_lista_seleccionada = arrayOf(
    lista_seleccionada[0].toString(), 
    lista_seleccionada[1]
)