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
 

IlCasti

Active Member
Licensed User
Longtime User
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

LucaMs

Expert
Licensed User
Longtime User
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

Mahares

Expert
Licensed User
Longtime User
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:
B4X:
"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

IlCasti

Active Member
Licensed User
Longtime User
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

IlCasti

Active Member
Licensed User
Longtime User
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
Top