CODEWITHSUNDEEP
androidmysqldatabasesqlite
DeNitE Appz
DeNitE Appz

Reputation: 1011

Complex sqLite query LIKE Clause

I'm having problems with a sqLite query in Android.

Basically what I want is this:

SELECT * FROM table
WHERE column2 LIKE ?
OR column3 LIKE ?
AND column1 =?
GROUP BY column2
ORDER BY column2;

This is my code:

String query = "SELECT * FROM " +
    DBHelper.DB_SEARCH_TABLE + " WHERE " +
    DBHelper.DB_SEARCH_SEARCHVALUE + " LIKE ? OR " +
    DBHelper.DB_SEARCH_ARTIST + " LIKE ? AND " +
    DBHelper.DB_SEARCH_TYPE + " =? GROUP BY " +
    DBHelper.DB_SEARCH_SEARCHVALUE + " ORDER BY " +
    DBHelper.DB_SEARCH_SEARCHVALUE + ";";

cursor = mp3Database.rawQuery(query, new String[]{"%" + searchTerm + "%", "%" + searchTerm + "%", type});

The query kind of works. It doesn't give any errors... but it also includes rows that are not of the type I am looking for... (AND column1 =?)

The LIKE clauses work great like this... it's just the type that's the problem.

Suggestions?

=========================

Is this a plausible query?

SELECT * FROM Search 
WHERE (SearchValue LIKE ? OR Artist LIKE ?) OR 
(Artist LIKE ? AND Artist LIKE ?) AND
Type =? 
GROUP BY SearchValue
ORDER BY Artist , SearchValue;

 String query = "SELECT * FROM " +
                DBHelper.DB_SEARCH_TABLE + " WHERE (" +
                DBHelper.DB_SEARCH_SEARCHVALUE + " LIKE ? OR " +
                DBHelper.DB_SEARCH_ARTIST + " LIKE ?) OR (" +
                DBHelper.DB_SEARCH_ARTIST + " LIKE ? AND " +
                DBHelper.DB_SEARCH_ARTIST + " LIKE ?) AND " +
                DBHelper.DB_SEARCH_TYPE + " =? GROUP BY " +
                DBHelper.DB_SEARCH_SEARCHVALUE + " ORDER BY " +
                DBHelper.DB_SEARCH_ARTIST + " , " +
                DBHelper.DB_SEARCH_SEARCHVALUE + ";";


        cursor = mp3Database.rawQuery(query, new String[]{"%" + searchTerm + "%", "%" + searchTerm + "%", "%" + searchTerm + "%", "%" + search
                + "%", type});

Upvotes: 0

Views: 101

Answers (1)

Cory Charlton
Cory Charlton

Reputation: 8938

You probably want to group your where clauses: WHERE (DBHelper.DB_SEARCH_SEARCHVALUE LIKE ? OR DBHelper.DB_SEARCH_ARTIST LIKE ?) AND DBHelper.DB_SEARCH_TYPE = ?

String query = "SELECT * FROM " +
    DBHelper.DB_SEARCH_TABLE + " WHERE (" +
    DBHelper.DB_SEARCH_SEARCHVALUE + " LIKE ? OR " +
    DBHelper.DB_SEARCH_ARTIST + " LIKE ?) AND " +
    DBHelper.DB_SEARCH_TYPE + " =? GROUP BY " +
    DBHelper.DB_SEARCH_SEARCHVALUE + " ORDER BY " +
    DBHelper.DB_SEARCH_SEARCHVALUE + ";";

cursor = mp3Database.rawQuery(query, new String[]{"%" + searchTerm + "%", "%" + searchTerm + "%", type});

Upvotes: 1

Related Questions