Error on SELECT*FROM statement android SQLite Data

Question

I have a Database With a foreign key I am using the SELECT * FROM statement but I keep getting an error when I try to view the database.

SELECT * FROM STATEMENT

 private final String MY_QUERY = "SELECT * FROM wwTable mLat INNER JOIN wwTimeTable _id ON wwTable._id=wwTimeTable.mName WHERE Table._id=wwTimetabel.mForeign";

MY KEYS

public static final String KEY_ROWID = "_id";
public static final String KEY_ROWIDLL = "_id";
public static final String KEY_ROWIDTIME = "_id";
public static final String KEY_NAME = "mName";
public static final String KEY_LAT = "mLat";
public static final String KEY_LON = "mLon";
public static final String KEY_HOUR = "mHour";
public static final String KEY_MINUT = "mMinut";
public static final String KEY_DAY = "mDay";
public static final String KEY_MONTH = "mMonth";
public static final String KEY_YEAR = "mYear";
public static final String KEY_FOREIGN = "mForeign";

ONCREATE EXECSQL

 @Override
        public void onCreate(SQLiteDatabase db) {
            // TODO Auto-generated method stub
            db.execSQL("CREATE TABLE " + WW_TABLE + 
                    " (" + KEY_ROWIDLL + " INTEGER PRIMARY KEY AUTOINCREMENT, " +
                    KEY_LAT + " TEXT NOT NULL, " + 
                    KEY_LON + " TEXT NOT NULL);");
            db.execSQL("CREATE TABLE " + WW_TIMETABLE + 
                    " (" + KEY_ROWIDTIME + " INTEGER PRIMARY KEY AUTOINCREMENT, " +
                    KEY_HOUR + " TEXT NOT NULL, " +
                    KEY_FOREIGN + " INTEGER," +
                    " FOREIGN KEY ("+KEY_FOREIGN+") REFERENCES "+WW_TABLE+" ("+KEY_ROWIDLL+") ON DELETE CASCADE);");

CURSOR RAWQUERY

  public Cursor fetchTime() {
        // TODO Auto-generated method stub
        //return ourdb.query(WW_TIMETABLE, new String[] {KEY_ROWIDTIME, KEY_HOUR, KEY_FOREIGN}, null, null, null, null, null);
        return ourdb.rawQuery(MY_QUERY, new String[]{String.valueOf(KEY_FOREIGN)});

    }

HOW I VIEW ALL OF IT INSIDE OF A LISTACTIVITY

private void fillData() {
     // Get all of the rows from the database and create the item list

    mTimeNotesCursor = mDbHelper.fetchTime();
    startManagingCursor(mTimeNotesCursor);
   // startManagingCursor(mNotesCursor);

    // Create an array to specify the fields we want to display in the list (only TITLE)
    String[] from = new String[]{WWDatabase.KEY_HOUR,WWDatabase.KEY_FOREIGN};

    //String[] fromTime = new String[]{WWDatabase.KEY_HOUR};


    // and an array of the fields we want to bind those fields to (in this case just text1)
    int[] to = new int[]{R.id.textView2, R.id.textView3};

   //int[] toTime = new int[]{R.id.textView4};

    // Now create a simple cursor adapter and set it to display
    SimpleCursorAdapter notes = 
        new SimpleCursorAdapter(this, R.layout.time_text_row, mTimeNotesCursor, from, to);

    setListAdapter(notes);
   // SimpleCursorAdapter notesTime = 

       //new SimpleCursorAdapter(this, R.layout.time_text_row, mTimeNotesCursor, fromTime, toTime);
        //setListAdapter(notesTime);
}

STACK TRACE

01-04 09:47:30.260: E/AndroidRuntime(273): FATAL EXCEPTION: main
01-04 09:47:30.260: E/AndroidRuntime(273): java.lang.RuntimeException: Unable to start activity ComponentInfo{www.freshapp.com.wherewhen.html/www.freshapp.com.wherewhen.html.TimeViewer}: android.database.sqlite.SQLiteException: near "Table": syntax error: , while compiling: SELECT * FROM wwTable mLat INNER JOIN wwTimeTable _id ON wwTable._id=wwTimeTable.mName WHERE Table._id=wwTimetabel.mForeign
01-04 09:47:30.260: E/AndroidRuntime(273):  at android.app.ActivityThread.performLaunchActivity(ActivityThread.java:2663)
01-04 09:47:30.260: E/AndroidRuntime(273):  at android.app.ActivityThread.handleLaunchActivity(ActivityThread.java:2679)
01-04 09:47:30.260: E/AndroidRuntime(273):  at android.app.ActivityThread.access$2300(ActivityThread.java:125)
01-04 09:47:30.260: E/AndroidRuntime(273):  at android.app.ActivityThread$H.handleMessage(ActivityThread.java:2033)
01-04 09:47:30.260: E/AndroidRuntime(273):  at android.os.Handler.dispatchMessage(Handler.java:99)
01-04 09:47:30.260: E/AndroidRuntime(273):  at android.os.Looper.loop(Looper.java:123)
01-04 09:47:30.260: E/AndroidRuntime(273):  at android.app.ActivityThread.main(ActivityThread.java:4627)
01-04 09:47:30.260: E/AndroidRuntime(273):  at java.lang.reflect.Method.invokeNative(Native Method)
01-04 09:47:30.260: E/AndroidRuntime(273):  at java.lang.reflect.Method.invoke(Method.java:521)
01-04 09:47:30.260: E/AndroidRuntime(273):  at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:868)
01-04 09:47:30.260: E/AndroidRuntime(273):  at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:626)
01-04 09:47:30.260: E/AndroidRuntime(273):  at dalvik.system.NativeStart.main(Native Method)
01-04 09:47:30.260: E/AndroidRuntime(273): Caused by: android.database.sqlite.SQLiteException: near "Table": syntax error: , while compiling: SELECT * FROM wwTable mLat INNER JOIN wwTimeTable _id ON wwTable._id=wwTimeTable.mName WHERE Table._id=wwTimetabel.mForeign
01-04 09:47:30.260: E/AndroidRuntime(273):  at android.database.sqlite.SQLiteCompiledSql.native_compile(Native Method)
01-04 09:47:30.260: E/AndroidRuntime(273):  at android.database.sqlite.SQLiteCompiledSql.compile(SQLiteCompiledSql.java:91)
01-04 09:47:30.260: E/AndroidRuntime(273):  at android.database.sqlite.SQLiteCompiledSql.<init>(SQLiteCompiledSql.java:64)
01-04 09:47:30.260: E/AndroidRuntime(273):  at android.database.sqlite.SQLiteProgram.<init>(SQLiteProgram.java:80)
01-04 09:47:30.260: E/AndroidRuntime(273):  at android.database.sqlite.SQLiteQuery.<init>(SQLiteQuery.java:46)
01-04 09:47:30.260: E/AndroidRuntime(273):  at android.database.sqlite.SQLiteDirectCursorDriver.query(SQLiteDirectCursorDriver.java:42)
01-04 09:47:30.260: E/AndroidRuntime(273):  at android.database.sqlite.SQLiteDatabase.rawQueryWithFactory(SQLiteDatabase.java:1345)
01-04 09:47:30.260: E/AndroidRuntime(273):  at android.database.sqlite.SQLiteDatabase.rawQuery(SQLiteDatabase.java:1315)
01-04 09:47:30.260: E/AndroidRuntime(273):  at www.freshapp.com.wherewhen.html.WWDatabase.fetchTime(WWDatabase.java:143)
01-04 09:47:30.260: E/AndroidRuntime(273):  at www.freshapp.com.wherewhen.html.TimeViewer.fillData(TimeViewer.java:57)
01-04 09:47:30.260: E/AndroidRuntime(273):  at www.freshapp.com.wherewhen.html.TimeViewer.onCreate(TimeViewer.java:41)
01-04 09:47:30.260: E/AndroidRuntime(273):  at android.app.Instrumentation.callActivityOnCreate(Instrumentation.java:1047)
01-04 09:47:30.260: E/AndroidRuntime(273):  at android.app.ActivityThread.performLaunchActivity(ActivityThread.java:2627)
01-04 09:47:30.260: E/AndroidRuntime(273):  ... 11 more

I would love any help on this. I have been looking for some time now.

Answer 1

Your where clause references an object called Table which you haven't got in your FROM clause. Also, there's not much point in aliasing your tables if you're not using the aliases..

I'm guessing it should be:

private final String MY_QUERY = "SELECT * FROM wwTable INNER JOIN wwTimeTable ON wwTable._id=wwTimeTable.mName WHERE wwTable._id=wwTimetable.mForeign"; 

Answer 2

Remove the mLat after the wwTable in the from statement.

Correct syntax is: select * from table inner join other_table ...