Re: Help to improve clause in SQL Select
Moderator: Rathinagiri
- Amarante
- Posts: 182
- Joined: Fri Apr 27, 2012 9:44 pm
- DBs Used: DBF, MySQL, MariaDB, SQLite, PostgreSQL
- Location: Araruama-RJ, Brazil
Re: Help to improve clause in SQL Select
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.
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
- Posts: 182
- Joined: Fri Apr 27, 2012 9:44 pm
- DBs Used: DBF, MySQL, MariaDB, SQLite, PostgreSQL
- Location: Araruama-RJ, Brazil
Re: Help to improve clause in SQL Select
I even answer me:Using your query as would add:
Where T2.nome like "maria%"
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
- Posts: 182
- Joined: Fri Apr 27, 2012 9:44 pm
- DBs Used: DBF, MySQL, MariaDB, SQLite, PostgreSQL
- Location: Araruama-RJ, Brazil
Re: Help to improve clause in SQL Select
The Database
Table Propriedade -> 369 records
Table Titulares -> 1074 records
Query from Rathinagiri
MySQL -> 369 records
SQLite -> 367 records
Does anyone know why the difference of 2 records between MySQL and SQLite, even using the same query?
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
SQLite -> 367 records
Does anyone know why the difference of 2 records between MySQL and SQLite, even using the same query?
- Rathinagiri
- Posts: 5471
- Joined: Tue Jul 29, 2008 6:30 pm
- DBs Used: MariaDB, SQLite, SQLCipher and MySQL
- Location: Sivakasi, India
- Contact:
Re: Help to improve clause in SQL Select
That can be found out only when we can separate those two records. What are the contents of those two?
East or West HMG is the Best.
South or North HMG is worth.
...the possibilities are endless.
South or North HMG is worth.
...the possibilities are endless.
- Amarante
- Posts: 182
- Joined: Fri Apr 27, 2012 9:44 pm
- DBs Used: DBF, MySQL, MariaDB, SQLite, PostgreSQL
- Location: Araruama-RJ, Brazil
Re: Help to improve clause in SQL Select
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.
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
- Posts: 182
- Joined: Fri Apr 27, 2012 9:44 pm
- DBs Used: DBF, MySQL, MariaDB, SQLite, PostgreSQL
- Location: Araruama-RJ, Brazil
Re: Help to improve clause in SQL Select
Rathinagiri,
A thousand apologies. The error was mine!
The SQLite table that tested contained only 367 records.
Your query is perfect.
A thousand apologies. The error was mine!
The SQLite table that tested contained only 367 records.
Your query is perfect.
- Rathinagiri
- Posts: 5471
- Joined: Tue Jul 29, 2008 6:30 pm
- DBs Used: MariaDB, SQLite, SQLCipher and MySQL
- Location: Sivakasi, India
- Contact:
Re: Help to improve clause in SQL Select
In my system, both MySQL and SQLite are returning 369 records. Which version of SQLite are you using?
East or West HMG is the Best.
South or North HMG is worth.
...the possibilities are endless.
South or North HMG is worth.
...the possibilities are endless.
- Rathinagiri
- Posts: 5471
- Joined: Tue Jul 29, 2008 6:30 pm
- DBs Used: MariaDB, SQLite, SQLCipher and MySQL
- Location: Sivakasi, India
- Contact:
Re: Help to improve clause in SQL Select
No problem.Amarante wrote:Rathinagiri,
A thousand apologies. The error was mine!
The SQLite table that tested contained only 367 records.
Your query is perfect.
East or West HMG is the Best.
South or North HMG is worth.
...the possibilities are endless.
South or North HMG is worth.
...the possibilities are endless.
- Pablo César
- Posts: 4059
- Joined: Wed Sep 08, 2010 1:18 pm
- Location: Curitiba - Brasil
Re: Re: Help to improve clause in SQL Select
Nice
HMGing a better world
"Matter tells space how to curve, space tells matter how to move."
Albert Einstein
"Matter tells space how to curve, space tells matter how to move."
Albert Einstein