SQLite query

David Crow

These two queries:

String sQuery1 = "SELECT * FROM service_schedules WHERE (vehicle_id,service_id) IN (SELECT DISTINCT vehicle_id,service_id FROM services_performed) AND vehicle_id=?";
Cursor cursor1 = db.rawQuery(sQuery1, new String[]{ String.valueOf(id) });

String sQuery2 = "SELECT * FROM service_schedules WHERE (vehicle_id,service_id) IN (SELECT DISTINCT vehicle_id,service_id FROM services_performed) AND vehicle_id="+id;
Cursor cursor2 = db.rawQuery(sQuery2, null);

only differ in how they handle the parameter at the end. For the second query, it compiles and executes fine. For the first query, the comma in the WHERE clause shows this error (in Android Studio): '(', ')', '.', BETWEEN or IN expected, got ',' Even if I remove the AND condition altogether:

String sQuery3 = "SELECT * FROM service_schedules WHERE (vehicle_id,service_id) IN (SELECT DISTINCT vehicle_id,service_id FROM services_performed)";
Cursor cursor3 = db.rawQuery(sQuery3, null);

the error persists. Since there are two columns in the WHERE clause and two columns in the subquery, I don't understand the issue. I also tried using aliases. [edit] I was able to use the third query in DB Browser for SQLite and it worked fine. That tells me that the syntax is correct and the problem is with how AS handles strings containing SQL queries. [/edit] Any ideas? Thanks. DC

"One man's wage rise is another man's price increase." - Harold Wilson

"Fireproof doesn't mean the fire will never come. It means when the fire comes that you will be able to withstand it." - Michael Simmons

"You can easily judge the character of a man by how he treats those who can do nothing for him." - James D. Miles

k5054

David Crow wrote:

String sQuery2 = "SELECT * FROM service_schedules WHERE (vehicle_id,service_id) IN (SELECT DISTINCT vehicle_id,service_id FROM services_performed) AND vehicle_id="+id;

This appears to be missing a + ")". Also, what's the value of id. Just doing a string concatenation could result in SQL injection, so that seems like a bad idea.

Keep Calm and Carry On

David Crow

k5054 wrote:

This appears to be missing a + ")".

All parenthesis are properly paired up.

k5054 wrote:

Also, what's the value of id.

It could be any number.

k5054 wrote:

Just doing a string concatenation could result in SQL injection, so that seems like a bad idea.

True, but that's not what the post is about.

"One man's wage rise is another man's price increase." - Harold Wilson

"Fireproof doesn't mean the fire will never come. It means when the fire comes that you will be able to withstand it." - Michael Simmons

"You can easily judge the character of a man by how he treats those who can do nothing for him." - James D. Miles

Richard Deeming

I can't find any documentation to suggest that Sqlite supports multi-value IN queries; that syntax only seems to apply to MySQL. Would an Exists query work instead?

SELECT * FROM service_schedules As s WHERE Exists(SELECT 1 FROM services_performed As p WHERE p.vehicle_id = s.vehicle_id And p.service_id = s.service_id) And s.vehicle_id = ?

"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer

David Crow

Richard Deeming wrote:

I can't find any documentation to suggest that Sqlite supports multi-value IN queries; that syntax only seems to apply to MySQL.

Given that the query works in both Android Studio and in DB Browser for SQLite, I'd say the syntax is supported.

Richard Deeming wrote:

Would an Exists query work instead?

I'll try it and see. [edit] The EXISTS query produces the same results as the second query in my initial post. Thanks. I'm still wondering what AS has against that first query string, though. [/edit]

"One man's wage rise is another man's price increase." - Harold Wilson

"Fireproof doesn't mean the fire will never come. It means when the fire comes that you will be able to withstand it." - Michael Simmons

"You can easily judge the character of a man by how he treats those who can do nothing for him." - James D. Miles