SQLite no such column even though everything spelled right and there is correct spacing

Question

I am learning android programming and sqlite at the same time. I am creating a table like:

    private static final String DATABASE_CREATE = "CREATE TABLE " + BOOK_TABLE + " ("
        + BookContract._ID + " INTEGER PRIMARY KEY AUTOINCREMENT, "
        + BookContract.TITLE + " TEXT NOT NULL, "
        + BookContract.AUTHORS + " TEXT NOT NULL, "
        + BookContract.ISBN + " TEXT NOT NULL, "
        + BookContract.PRICE + " TEXT NOT NULL );";

In my function that fetches all books in the database, my query is:

String query =  "SELECT " +
            BOOK_TABLE + "." + BookContract._ID + ", " +
            BookContract.TITLE + ", " +
            BookContract.PRICE + ", " +
            BookContract.ISBN + ", " +
            "GROUP_CONCAT(" + AuthorContract.NAME + ", ',') as " + BookContract.AUTHORS +
            " FROM " + BOOK_TABLE + " LEFT OUTER JOIN " + AUTHOR_TABLE + " ON " +
            BOOK_TABLE + "." + BookContract._ID + "=" +
            AUTHOR_TABLE + "." + AuthorContract.BOOK_FOREIGN_KEY +
            " GROUP BY " + BOOK_TABLE + "." + BookContract._ID + ", " +
            BookContract.TITLE + ", " + BookContract.PRICE + ", " + BookContract.ISBN;

Here is how the string actually looks like for the table creation:

CREATE TABLE bookTable (_id INTEGER PRIMARY KEY AUTOINCREMENT, title TEXT NOT NULL, authors TEXT NOT NULL, isbn TEXT NOT NULL, price TEXT NOT NULL );

and for the query:

 SELECT bookTable._id, title, price, isbn, GROUP_CONCAT(name, ',') as authors FROM bookTable LEFT OUTER JOIN authorTable ON bookTable._id = authorTable.book_fk GROUP BY bookTable._id, title, price, isbn

I swear I am going insane on this because similar stackOverflow posts asking the similar problem have minor spelling mistakes or missing spaces, however mine seems to work correctly on an online sql simulator!

When i do a database.rawQuery(query, null), the error saying that there is no such column: price appears in the log.

EDIT:

There is another Author table that has the missing name column that was mentioned in the comments, here it is for clarity:

 String AUTHOR_TABLE_CREATE = "CREATE TABLE " + AUTHOR_TABLE + " ("
        + AuthorContract._ID + " INTEGER PRIMARY KEY AUTOINCREMENT, "
        + AuthorContract.BOOK_FOREIGN_KEY + " INTEGER NOT NULL, "
        + AuthorContract.NAME  + " TEXT NOT NULL, "
        + "FOREIGN KEY ("+ AuthorContract.BOOK_FOREIGN_KEY+") " +
        "REFERENCES "+BOOK_TABLE+"("+BookContract._ID+") ON DELETE CASCADE);";

which gives the string:

CREATE TABLE authorTable (_id INTEGER PRIMARY KEY AUTOINCREMENT, book_fk INTEGER NOT NULL, name TEXT NOT NULL, FOREIGN KEY (book_fk) REFERENCES bookTable(_id) ON DELETE CASCADE);

Are you creating the database first than execute the above query.. I have also stuck in the same problem earlier that... i have not create the database and fire the query...plz check it first that....you are creating the database first and than executing the query — Ravindra Kushwaha, Feb 08 '16 at 05:27
@RavindraKushwaha I open the database first and then call the fetchAllBooks() function so I think it is creating the database first and then executing. What I find weird is that why is only price not being recognized and the others are. — user3261941, Feb 08 '16 at 05:40
@user3261941 did you try removing price column from select and see what happen? — Gueorgui Obregon, Feb 08 '16 at 05:40
try adding bookTable.isbn, bookTable.price to each corresponding element on the query... — Gueorgui Obregon, Feb 08 '16 at 05:43
@g2o gvies same error but says no such column: bookTable.price now — user3261941, Feb 08 '16 at 05:48
Try this : SELECT bt._id, bt.title, bt.price, bt.isbn, GROUP_CONCAT(at.name, ',') as authors FROM bookTable bt LEFT OUTER JOIN authorTable at ON bt._id = at.book_fk GROUP BY bt._id, bt.title, bt.price, bt.isbn — Rakesh Soni, Feb 08 '16 at 05:49
@RakeshSoni that was the query I had after doing g2o's reccommendation. It gave the same error. — user3261941, Feb 08 '16 at 05:53

score 2 · Accepted Answer · edited Feb 08 '16 at 14:47

2

Just uninstall your app from mobile and then re-run. i hope it works if there is no problem in code.

edited Feb 08 '16 at 14:47

Ravindra Kushwaha

7,846
14
53
103

answered Feb 08 '16 at 05:52

THIS WAS IT!! This fixed the solution. Many thanks to the others who helped too. – user3261941 Feb 08 '16 at 05:55

SQLite no such column even though everything spelled right and there is correct spacing

1 Answers1