Android Question Record absence in SqLite table

[Sergey_New]

Returning some records from a table

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.

 

[JCO]

If I understand your request correctly, it would be something like this:

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:

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

 

[Mahares]

This is how I would do it:

Dim str As String "Sergey"
    rs = SQL1.ExecQuery2("SELECT key FROM tags WHERE person= ? and key NOT LIKE ?", Array As Object(1, $"%${str}%"$))

 

[Sergey_New]

Many thanks to JCO and Mahares!
And using the operator EXISTS can be faster?

 

[JCO]

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

 

[Sergey_New]

Need key exactly equal to str.

 

[JCO]

Many thanks to JCO and Mahares!
And using the operator EXISTS can be faster?

AFAIK, EXISTS should only be used when working with subqueries

 

[Sergey_New]

used when working with subqueries

I understand.

 

[Sergey_New]

P.S.
I would like the search to stop at the first mismatch

 

[Mahares]

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

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"

 

[Sergey_New]

Mahares,​

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

 

[Mahares]

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

 

[Sergey_New]

This is what I need. Figured it out: replaced "<>" with "="

rs = sql1.ExecQuery2("SELECT key FROM tags WHERE person=? AND key=? LIMIT 1", Array(person, str))

 

[Mahares]

And using the operator EXISTS can be faster?

There are ways to improve performance of queries. If you have one DB that is giving you trouble in terms of performance and you are receptive to post it or its project and show what slows it down, I am sure members will take a stab at it and report back to you.

 

[Sergey_New]

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!