Win a copy of Kotlin in Action this week in the Kotlin forum!
  • Post Reply Bookmark Topic Watch Topic
  • New Topic

Using multiple criteria with Where/IN clause in Sqlite  RSS feed

 
Josh Herron
Ranch Hand
Posts: 39
1
  • Likes 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I have spent the past few days working with Sqlite and I am able to successfully insert and update records and pull records from my test DB.   But I'm having a hard time pulling back records when multiple criteria are needed.

below you can see an example of what I'm trying to do but no matter how I build the list of values to replace the variable in the SQL string the query fails.   If I manually run the same query in my DB browser I get back results.  I assume there is a trick to chaining multiple values in Sqlite statements?

An example query I was running is SELECT * from ChannelData WHERE group IN ('HBO', 'SHOWTIME', 'STARZ')

Any suggestions would be greatly appreciated.

 
Jeanne Boyarsky
author & internet detective
Sheriff
Posts: 37230
519
Eclipse IDE Java VI Editor
  • Likes 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Josh,
Good question. The ? can only be used to replace a single value. So the query you built is trying to match the list values including commas - not the intent. I wrote a Journal article with the options for dealing with this problem. The article is 10 years old, but it works exactly the same way today.
 
Josh Herron
Ranch Hand
Posts: 39
1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Great!  Thank you.
 
Pete Letkeman
Ranch Foreman
Posts: 612
16
Android Chrome IntelliJ IDE Java MySQL Database
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I know that you are just starting out, but here is a quick note to keep in mind.

It is generally slower to do a select * from then to do a select field1, field2, field3 from.
Yes we could be talking about milliseconds or nanoseconds, but they do add up.
The reason is is slower is because first the database engine needs to query to get all of the field names.
Once that is done then the select statement is run with the * being replaced with the field names.
Doing a select * also retrieves all of the fields and some times you only need two or three of the fields so why select them all only to not use all of them?

Again, this may not be too big of a concern, however you should be aware of this when you get into executing many selects.
 
Josh Herron
Ranch Hand
Posts: 39
1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thanks Pete, I was just simplifying the select statement for troubleshooting the IN statement. 

I planned to refine to only select the 5 columns I actually need once I get the multiple IN logic working.   I have found in this journey that I'm most successful if I keep it simple in the beginning and then build from there.    Once I get some experience hopefully I can stop doing that and just do it right the first time.   LOL
 
Pete Letkeman
Ranch Foreman
Posts: 612
16
Android Chrome IntelliJ IDE Java MySQL Database
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Josh Herron wrote:I have found in this journey that I'm most successful if I keep it simple in the beginning and then build from there.

I agree 100% with this.
And like I said, I know that you are a beginner.

Some people who have been doing SQL related operations/queries for years still do select * in their code.
And when I'm working directly with the database console I do select * more often then not when creating the query.

Josh Herron wrote:Once I get some experience hopefully I can stop doing that and just do it right the first time..

Right the first time, or wrong the first time, that does not matter that much. What and where it matters is if production code has it wrong or inefficient.
At least that is how I see it.
 
Pete Letkeman
Ranch Foreman
Posts: 612
16
Android Chrome IntelliJ IDE Java MySQL Database
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Well I guess, if you continue to do it incorrectly or inefficiently then you could be wasting time and resources so this could matter in a non production environment as well, just not that much as in a production environment.
 
Jeanne Boyarsky
author & internet detective
Sheriff
Posts: 37230
519
Eclipse IDE Java VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Pete Letkeman wrote:Yes we could be talking about milliseconds or nanoseconds, but they do add up.
The reason is is slower is because first the database engine needs to query to get all of the field names.

If the database is on a different server, it is way more than milliseconds because the extra fields need to be transferred across the network. That adds up to real time!
 
It is sorta covered in the JavaRanch Style Guide.
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
Boost this thread!