INNER JOIN WITH OR CLAUSE

Asked

Viewed 337 times

2

I have the following select:

SELECT linha._id, 
       linha.numero_onibus, 
       linha.nome, 
       percurso.nome 
FROM linha
INNER JOIN percurso ON linha._id = percurso.id_linha
AND (linha.numero_onibus LIKE ? OR linha.nome LIKE ?) COLLATE NOCASE

When the condition linha.numero_onibus LIKE ? is false, the query does not return results. What and have to do to that, even if the condition linha.numero_onibus LIKE ? is false, he proceed with the consultation and return me the results of linha.nome LIKE ? ?

sqlite

  • Your query looks OK, except for the second part of the ON, which should be on WHERE. But this makes no difference in the result in some Sgbds (not sure how it is in sqlite, so it is worth testing.

  • I believe the problem occurred by query logic. The condition linha.numero_onibus LIKE ? OR linha.nome LIKE ? will always bring results of linha.numero_onibus LIKE ? because this condition will always be true. As I was also having performance problems, I ended up creating a table FTS3 to solve the query problem.

1 answer

0


Quick response (you will need to test):

 SELECT
 linha._id, 
 linha.numero_onibus, 
 linha.nome, 
 percurso1.nome as the_nome_caso_1,
 percurso2.nome as the_nome_caso_2

 FROM linha

 LEFT OUTER JOIN percurso as percurso1 ON linha._id = percurso1.id_linha
 AND linha.nome LIKE ?

 LEFT OUTER JOIN JOIN percurso as percurso2 ON linha._id = percurso2.id_linha
 AND linha.numero_onibus LIKE ?

Then you will use twice the route table, but with "alias".

If you use INNER JOIN and the query gives a false result, there will be no result.

  • I already tried with LEFT OUTER JOIN and still did not bring results. The query only works when running on sqlite, when implement on android it does not return me values. line.name, CASE WHEN P1.name IS NOT NULL THEN P1.name ELSE P2.name END AS path FROM line LEFT OUTER JOIN course AS P1 ON line. _id = P1.id_line AND line.numero_onibus LIKE '%10%' LEFT OUTER JOIN course AS P2 ON line. _id = P2.id_line AND line.name LIKE '%10%' WHERE P1.name IS NOT NULL or P2.name IS NOT NULL

  • Ah, so if you have resulted in the "off" sqlite of Android, but have not rolled "on" Android, the problem is not the query. You need to check if the connection is coreta and also if you have value in the database. For this, and better download the sqlite data on your computer to check. For this, if you use Android Studio, you need to take a look at the window, on Android DDMS. You can see the data from tablets. The sqlLite data is in:

  • Sqlite data are in: data/data/com.xxxx.module/Databases/com.xxxxxx.module.database

  • 1

    It seems kind of silly to say this, but check out the Sqlite version..

  • I noticed after a long time, that in many cases we are seeking a response far away from us, when the right answer is very close. So Daniel’s answer is an interesting step!

Browser other questions tagged sqlite

You are not signed in. Login or sign up in order to post.