SQLite的，桌子，加入 [英] SQLite, Tables, Joining

问题描述

Android的，实施的SQLite

Android, implementing SQLite

这是我有表：

http://postimg.org/image/jafsx39h7/

我有code：

public String getWorkoutNameInfo() {
    // TODO Auto-generated method stub

    String[] columns = new String[] { KEY_DATE_OF_WORKOUT,
            KEY_WORKOUT_NAME, KEY_DATE };           
    Cursor c = ourDatabase.query(TABLE_DATE_WORKOUT, columns, null, null,           
            null, null, null, null);                    
    String workoutName2 = "";               
    int iWorkoutID = c.getColumnIndex(KEY_WORKOUT_NAME);
    for (c.moveToFirst(); !c.isAfterLast(); c.moveToNext()) {
        workoutName2 = workoutName2 + c.getString(iWorkoutID);  

            }

        return workoutName2;

}

这将返回workoutName2这是'2 1 2

This returns workoutName2 which is '2 1 2'

现在我需要查找2的值在我WorkoutTable和返回后退，并产生回，胸，背这样我就可以了把那个到我的屏幕，而不是2 1 2。

Now i need to look up the value for 2 in my WorkoutTable and return 'Back' and produce 'Back,Chest,Back' so i can out put that onto my screen instead of 2 1 2.

我明白我将使用一个JOIN语句？但是我遇到没有运气实现它。

I understand I'll be using a JOIN statement? However i'm having no luck implementing it.

我的表编码：

// WORKOUT TABLE - COLUMN NAMES
public static final String KEY_WORKOUT = "workout_id";
public static final String STRING_WORKOUT = "workout_name"; 


    // DATE OF WORKOUT TABLE - COLUMN NAMES
public static final String KEY_DATE_OF_WORKOUT = "date_of_workout_id";
public static final String KEY_DATE = "date_of_workout";
public static final String KEY_WORKOUT_NAME = "workout_id";

    //TABLE NAMES
private static final String TABLE_WORKOUT = "WorkoutTable";
private static final String TABLE_DATE_WORKOUT = "DateofWorkout";

下面是我的尝试：

public String test(String workoutSelectedNameInfo) {

    // TODO Auto-generated method stub
    String NAMEofWorkout = "";
    open();
    ourDatabase = ourhelper.getReadableDatabase();
    Cursor c = ourDatabase
            .rawQuery(
                    "SELECT * FROM WorkoutTable LEFT JOIN DateofWorkout ON (WorkoutTable.workout_id = DateofWorkout.workout_id)   WHERE workout_id = ?",
                    new String[] { "2" });



    int iDateofWorkoutsWorkoutId = c.getColumnIndex(KEY_WORKOUT_NAME);
    for (c.moveToFirst(); !c.isAfterLast(); c.moveToNext()) {
        NAMEofWorkout = NAMEofWorkout + c.getString(iDateofWorkoutsWorkoutId);
    }
    c.close();
    ourDatabase.close();
    System.out.println(NAMEofWorkout);
    return NAMEofWorkout;

}

然而，当它输出NameofWorkout这应该是胸部，背部，胸部我收到什么都没有，绝对空白。

HOWEVER When it output the 'NameofWorkout' which SHOULD be Chest,Back,Chest i receive nothing at all, absolutely blank.

推荐答案

当我输入数据到一个SQLite的实例，我的机器上，然后执行查询，我得到以下错误：

When I enter your data into a SQLite instance on my machine and then execute your query, I get the following error:

错误：不明确的列名：workout_id

Error: ambiguous column name: workout_id

在查询中，改变 WHERE workout_id 到 WHERE DateofWorkout.workout_id 使错误走了。

In the query, changing WHERE workout_id to WHERE DateofWorkout.workout_id makes the error go away.

此问题发生，因为有在结果中名为 workout_id 两列，并且必须消除歧义的后续引用。正因为如此，你可能还必须更改

This problem happens because there are two columns named workout_id in the result, and you must disambiguate your subsequent references. Because of this, you probably must also change

c.getColumnIndex（KEY_WORKOUT_NAME）

到

c.getColumnIndex（TABLE_DATE_WORKOUT +。+ KEY_WORKOUT_NAME）

当你使用效果更高版本。

when you make use of the results later.

这篇关于SQLite的，桌子，加入的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！