Re: Help to improve clause in SQL Select

Amarante · Post by **Amarante** » Sat Apr 11, 2015 3:46 pm

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.

Amarante · Post by **Amarante** » Sat Apr 11, 2015 7:32 pm

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

Amarante · Post by **Amarante** » Sun Apr 12, 2015 7:01 pm

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?

Post by **Rathinagiri** » Sun Apr 12, 2015 7:42 pm

That can be found out only when we can separate those two records.

What are the contents of those two?

Amarante · Post by **Amarante** » Sun Apr 12, 2015 8:02 pm

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.

Amarante · Post by **Amarante** » Mon Apr 13, 2015 3:29 am

Rathinagiri,
A thousand apologies. The error was mine!

The SQLite table that tested contained only 367 records.

Your query is perfect.

Post by **Rathinagiri** » Mon Apr 13, 2015 3:40 am

In my system, both MySQL and SQLite are returning 369 records. Which version of SQLite are you using?

Post by **Rathinagiri** » Mon Apr 13, 2015 3:42 am

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.

Pablo César · Post by **Pablo César** » Fri Apr 17, 2015 8:46 pm

HMGforum.com