Android Question Record absence in SqLite table

Sergey_New

Well-Known Member
Licensed User
Longtime User
Returning some records from a table
B4X:
sql1.Initialize(my_path, "my_tags.db", False)
Dim rs As ResultSet
Dim str As String="xxx"
rs = sql1.ExecQuery("SELECT key FROM tags WHERE person=1")
Please help me change rs to detect the absence of str in the key field.
 
Solution
If I understand your request correctly, it would be something like this:

B4X:
sql1.Initialize(my_path, "my_tags.db", False)
Dim rs As ResultSet
Dim str As String="xxx"
rs = sql1.ExecQuery2("SELECT key FROM tags WHERE person=1 AND key<>?", Array(str))

Or better:

B4X:
Dim str As String="xxx"
Dim person as int = 1
rs = sql1.ExecQuery2("SELECT key FROM tags WHERE person=? AND key<>?", Array(person, str))

JCO

Active Member
Licensed User
Longtime User
If I understand your request correctly, it would be something like this:

B4X:
sql1.Initialize(my_path, "my_tags.db", False)
Dim rs As ResultSet
Dim str As String="xxx"
rs = sql1.ExecQuery2("SELECT key FROM tags WHERE person=1 AND key<>?", Array(str))

Or better:

B4X:
Dim str As String="xxx"
Dim person as int = 1
rs = sql1.ExecQuery2("SELECT key FROM tags WHERE person=? AND key<>?", Array(person, str))
 
Upvote 0
Solution

Sergey_New

Well-Known Member
Licensed User
Longtime User
Many thanks to JCO and Mahares!
And using the operator EXISTS can be faster?
 
Upvote 0

JCO

Active Member
Licensed User
Longtime User
Just to clarify.
My line would exclude only records where key is exactly str
Mahares' line would exclude any record where the key contains str
Depending on what you need, use one or the other
 
Upvote 0

Sergey_New

Well-Known Member
Licensed User
Longtime User
P.S.
I would like the search to stop at the first mismatch
 
Last edited:
Upvote 0

Mahares

Expert
Licensed User
Longtime User
would like the search to stop at the first mismatch
Mahares' line would exclude any record where the key contains str

Add LIMIT 1 at the end of the query. See below
B4X:
Dim str As String "Sergey"
    rs = SQL1.ExecQuery2("SELECT key FROM tags WHERE person= ? and key NOT LIKE ? LIMIT 1", Array As Object(1, $"%${str}%"$))
Please note that the code I posted also excludes the EXACT search and also besides anywhere in the column.
If you use JCO's code the query will be: "SELECT key FROM tags WHERE person=? AND key<>? LIMIT 1"
 
Upvote 0

Sergey_New

Well-Known Member
Licensed User
Longtime User

Mahares,​

This query returns the first row of the table on a mismatch, not an empty value.
 
Upvote 0

Mahares

Expert
Licensed User
Longtime User
This query returns the first row of the table on a mismatch, not an empty value.
If for instance, you have 5 that do not match, it will only show the first of the 5 records. That is how I understood the question. If not, please explain, preferably with an example.
Added this: If you have keyx that have a empty string and you want to display it, as the first one, add this to the query: before LIMIT 1:
ORDER BY keyx LIMIT 1
 
Last edited:
Upvote 0

Sergey_New

Well-Known Member
Licensed User
Longtime User
This is what I need. Figured it out: replaced "<>" with "="
B4X:
rs = sql1.ExecQuery2("SELECT key FROM tags WHERE person=? AND key=? LIMIT 1", Array(person, str))
 
Upvote 0

Sergey_New

Well-Known Member
Licensed User
Longtime User
So far, I do not have an urgent need to improve performance. Wanted to get the best solution.
Mahares, thank you for in resolving my issues!
 
Upvote 0
Top