Android Question SQL Multiple Join Errors

IlCasti

Active Member
Licensed User
Longtime User
Hi all
I have a sql database with 4 tables
When i start this query:

SELECT Allenatori.NOME_ALLENATORE FROM Allenatori INNER JOIN Save ON Allenatori.ID_ALLENATORE = Save.ID_ALLENATORE

all is fine, nothing wrong, no errors

When i start this one:

SELECT Allenatori.NOME_ALLENATORE, Squadre.NOME_SQUADRA FROM Squadre INNER JOIN (Allenatori INNER JOIN Save ON Allenatori.ID_ALLENATORE = Save.ID_ALLENATORE) ON Squadre.ID_SQUADRA = Save.ID_SQUADRA

i receive this error:
android.database.sqlite.SQLiteException: no such column: Allenatori.NOME_ALLENATORE (code 1): , while compiling: SELECT Allenatori.NOME_ALLENATORE, Squadre.NOME_SQUADRA FROM Squadre INNER JOIN (Allenatori INNER JOIN Save ON Allenatori.ID_ALLENATORE = Save.ID_ALLENATORE) ON Squadre.ID_SQUADRA = Save.ID_SQUADRA

How is it possible?
A BIG THANKS in advance to help me to understand

IlCasti
 
SOLVED

Qe = "SELECT Allenatori.NOME_ALLENATORE, TipoPartita.TIPO_PARTITA, Squadre.NOME_SQUADRA " & _
"FROM Allenatori " & _
"JOIN Save ON Allenatori.ID_ALLENATORE = Save.ID_ALLENATORE " & _
"JOIN Squadre ON Squadre.ID_SQUADRA = Save.ID_SQUADRA " & _
"JOIN TipoPartita ON TipoPartita.ID_TIPO_PARTITA = Save.ID_TIPO_PARTITA " & _
"GROUP BY Allenatori.NOME_ALLENATORE, TipoPartita.TIPO_PARTITA, Squadre.NOME_SQUADRA"

I didn't think sintax change too much from access :oops:
Bye
 
Upvote 0
Sorry if I'm intruding again.

It is unnecessary to name Allenatori.NOME_ALLENATORE, simply Allenatori.Nome, it is part of the table Allenatori!

Same thing for the names of the other fields:

Squadre.NOME_SQUADRA ---> Squadre.Nome

etc.
 
Upvote 0
In addition to Luca's suggestion to shorten table names when you have multi-joins is to use aliases for the table names. That makes it easier to read. Here is an example:
Code: Copy
"SELECT T1.Country, T2.City, T1.Population, T2.Soccer_team FROM Table1 T1 INNER JOIN Table2 T2 ON T1.ID=T2.ID" _
& " ORDER BY T1.population DESC"
 
Upvote 0
Something is not clear.

You wrote that you have tested the query with SQLite Database Browser!?

Anyway, it is better to shorten the names of tables/fields using aliases, when queries are long and complex.

Yes if you want i can post a print screen..
Pheraps sintax is wrong for library sql
I don t know
Bye
 
Upvote 0
Thank you to all for suggestions
I know alias and short name and probably i ll use it in the future
It was a test to try SQL library to popolate scrollview
Bye
 
Upvote 0
Back
Top