Page 3 of 3
Re: Help to improve clause in SQL Select
Posted: Sat Apr 11, 2015 3:46 pm
by Amarante
Rathinagiri,
Got it, although there are no records with values not zero.
What intrigued me most is that when I use your example in SQLite, the result returns with 2 records less.
Taking advantage of their knowledge, I ask your help.
Using your query as would add:
Where T2.nome like "maria%"
Thanks in advance.
Re: Help to improve clause in SQL Select
Posted: Sat Apr 11, 2015 7:32 pm
by Amarante
Using your query as would add:
Where T2.nome like "maria%"
I even answer me:
Code: Select all
SELECT Propriedade.Inscricao,
Propriedade.Titular_1, Case When Propriedade.Titular_1 = 0 Then '' Else ( select t1.nome from Titulares t1 where T1.Titular_Codigo = Propriedade.Titular_1 ) End,
Propriedade.Titular_2, Case When Propriedade.Titular_2 = 0 Then '' Else ( select t2.nome from Titulares t2 where T2.Titular_Codigo = Propriedade.Titular_2 ) End,
Propriedade.Titular_3, Case When Propriedade.Titular_1 = 0 Then '' Else ( select t3.nome from Titulares t3 where T3.Titular_Codigo = Propriedade.Titular_3 ) End
FROM Propriedade, Titulares T2
where T2.nome like "maria%" and Propriedade.Titular_2 = T2.Titular_codigo
ORDER BY Propriedade.Inscricao
Re: Help to improve clause in SQL Select
Posted: Sun Apr 12, 2015 7:01 pm
by Amarante
The Database
Table Propriedade -> 369 records
Table Titulares -> 1074 records
Query from Rathinagiri
Code: Select all
SELECT Propriedade.Inscricao,
Propriedade.Titular_1, Case When Propriedade.Titular_1 = 0 Then '' Else ( select t1.nome from Titulares t1 where T1.Titular_Codigo = Propriedade.Titular_1 ) End,
Propriedade.Titular_2, Case When Propriedade.Titular_2 = 0 Then '' Else ( select t2.nome from Titulares t2 where T2.Titular_Codigo = Propriedade.Titular_2 ) End,
Propriedade.Titular_3, Case When Propriedade.Titular_1 = 0 Then '' Else ( select t3.nome from Titulares t3 where T3.Titular_Codigo = Propriedade.Titular_3 ) End
FROM Propriedade
MySQL ->
369 records
SQLite ->
367 records
Does anyone know why the difference of 2 records between MySQL and SQLite, even using the same query?
Re: Help to improve clause in SQL Select
Posted: Sun Apr 12, 2015 7:42 pm
by Rathinagiri
That can be found out only when we can separate those two records.
What are the contents of those two?
Re: Help to improve clause in SQL Select
Posted: Sun Apr 12, 2015 8:02 pm
by Amarante
Rathinagiri,
Thanks for answering.
The records are in the test base I posted here:
http://hmgforum.com/viewtopic.php?f=37&t=4295#p40834
I set you apart for you:
"Inscricao" "titular_1" "titular_2" "titular_3"
"372" "1082" "0" "0"
"373" "1082" "0" "0"
This Table has only 369 records. In your query MySQL works well, but the two records do not appear in SQLite.
I plan to use the query in a mass to reach thousands of records.
The base will work on either MySQL as in SQLite.
So I was amazed at the failure of 2 records to SQLite.
Thank you in advance.
That graces fall on you for their infinite patience.
Re: Help to improve clause in SQL Select
Posted: Mon Apr 13, 2015 3:29 am
by Amarante
Rathinagiri,
A thousand apologies. The error was mine!
The SQLite table that tested contained only 367 records.
Your query is perfect.
Re: Help to improve clause in SQL Select
Posted: Mon Apr 13, 2015 3:40 am
by Rathinagiri
In my system, both MySQL and SQLite are returning 369 records. Which version of SQLite are you using?
Re: Help to improve clause in SQL Select
Posted: Mon Apr 13, 2015 3:42 am
by Rathinagiri
Amarante wrote:Rathinagiri,
A thousand apologies. The error was mine!
The SQLite table that tested contained only 367 records.
Your query is perfect.
No problem.
Re: Re: Help to improve clause in SQL Select
Posted: Fri Apr 17, 2015 8:46 pm
by Pablo César
Nice