如何在SQLite中联接三个表?我有三个表,一个是Info,第二个是workForce,第三个是workDetails

为了加入这三个表,我在表WorkForce中添加了表信息的外键,在表WorkDetails中添加了表WorkForce的外键。

表格信息:id(PK),name,status,date,weather

表WorkForce:id1(PK), subContractors,noOfPeople,noOfHours,TInfo_id(FK to table Info)

表工作详细信息:id2(PK),project,workDescription, Twf_id(FK to table workForce),TableInfo_id(FK to table Info) //contains multiple row

表信息

 ID          NAME        Weather        Date     Status
 ----------  ----------  ----------  ----------  ----------
    1           Paul        Sunny         15/10      MC
    2           Allen       Rainy         15/10      Working


表工作力

ID1          SubContractors   NoOfPeople      NoOfHours        TInfo_id
----------  --------------   ----------       ----------     -----------
1           AAA                2                 2                 1
2           BBB                3                 1                 2


表工作细节

ID2         Project       WorkDescription        TableInfo_id      Twf_id
----------  ----------     --------------          ----------    ----------
1              A               B                       1             1
2                                                      1             1
3                                                      1             1
4                                                      1             1
5               C               D                      2             2
6                                                      2             2
7                                                      2             2
8                                                      2             2


假设名称为Paul,因此将检索ID为1和TableInfo_id为1的所有行。

现在,我想使用cursor将它们检索到tableView中。以下是我的代码段。

MyDatabaseHelper.java

public class MyDatabaseHelper extends SQLiteOpenHelper {
    public static final int DATABASE_VERSION=1;
    public static final String DATABASE_NAME="131.db";
    public static final String TABLE_INFO="Information";
    public static final String TABLE_WORKFORCE="WorkForce";
    public static final  String TABLE_WORKDETAILS="WorkDetails";
    public static final String Subcontractors="subcontractors";
    public static final String NumberOfPerson="numberOfPerson";
    public static final String NumberOfHours="numberOfHours";
    public static final String ID="id";
    public static final String ID1="id1";
    public static final String ID2="id2";
    public static final String Name="name";
    public static final String Weather="weather";
    public static final String Date="date";
    public static final String Project="project";
    public static final String WorkDescription="workDescription";
    public static final String Status="status";
    public static final String TableInfo_id="tableInfo_id";
    public static final String TInfo_id="tInfo_id";
    public static final String Twf_id="tWf_id";



    public void onCreate(SQLiteDatabase db) {
        db.execSQL("create table " + TABLE_INFO + "(ID INTEGER PRIMARY KEY ,Name TEXT,Weather TEXT, Date DATETIME, Status Text)");
        db.execSQL("create table " + TABLE_WORKFORCE + "(ID INTEGER PRIMARY KEY  ,Subcontractors TEXT,NumberOfPerson INTEGER,NumberOfHours TEXT,TInfo_id INTEGER, FOREIGN KEY(TInfo_id) REFERENCES "+TABLE_INFO+"(ID))");
        db.execSQL("create table " + TABLE_WORKDETAILS + "(ID INTEGER PRIMARY KEY , Project TEXT, WorkDescription TEXT,TableInfo_id INTEGER, Twf_id INTEGER, FOREIGN KEY(Twf_id) REFERENCES "+TABLE_WORKFORCE+"(ID), FOREIGN KEY(TableInfo_id) REFERENCES "+TABLE_INFO+"(ID))");
    }


DisplayData.java

     public void onCreate(Bundle savedInstanceState) {
            super.onCreate(savedInstanceState);
            setContentView(R.layout.displaydata);
            MyDatabaseHelper db = new MyDatabaseHelper(this);
            InfoAPI I1 = new InfoAPI(this);
            sqlcon = new InfoAPI(this);
            setRequestedOrientation(ActivityInfo.SCREEN_ORIENTATION_LANDSCAPE);
            table_layout = (TableLayout) findViewById(R.id.tableLayout1);
            final String name1 = getIntent().getExtras().getString("name"); //the name were pass from another activity
              BuildTable(name1);

        }


        private void BuildTable(String name)
        {

            sqlcon.open();
            Cursor c = sqlcon.readEntry(name);

            int rows = c.getCount();
            int cols = c.getColumnCount();

            c.moveToFirst();

            // outer for loop
            for (int i = 0; i < rows; i++) {

                TableRow row = new TableRow(this);
                row.setLayoutParams(new ViewGroup.LayoutParams(ViewGroup.LayoutParams.MATCH_PARENT,
                        ViewGroup.LayoutParams.WRAP_CONTENT));

                // inner for loop
                for (int j = 0; j < cols; j++) {

                    TextView tv = new TextView(this);
                    tv.setLayoutParams(new TableRow.LayoutParams(
                            ViewGroup.LayoutParams.WRAP_CONTENT,
                            ViewGroup.LayoutParams.WRAP_CONTENT
                    ));
                    tv.setBackgroundResource(R.drawable.cell_shape);
                    tv.setGravity(Gravity.CENTER);
                    tv.setTextSize(18);
                    tv.setPadding(0, 5, 0, 5);

                    tv.setText(c.getString(j));

                    row.addView(tv);

                }

                c.moveToNext();

                table_layout.addView(row);

            }
            sqlcon.close();
        }

            }

**InfoAPI.java**

        public Cursor readEntry(String name) {

      //  String selectQuery = ("SELECT Weather,Date,Status,SubContractors,NumberOfPeople,NumberOfHours,TimeIn,TimeOut FROM "+
        //       MyDatabaseHelper.TABLE_INFO+MyDatabaseHelper.TABLE_WORKFORCE+MyDatabaseHelper.TABLE_WORKDETAILS+ "WHERE Name= ? AND"+MyDatabaseHelper.ID=MyDatabaseHelper.ID1+ "AND"+MyDatabaseHelper.ID=MyDatabaseHelper.TableInfo_id);
       return database.rawQuery("SELECT Information.weather, Information.date,Information.status"+"WorkForce.subContractors," +
               "WorkForce.noOfPeople,WorkForce.noOfHours"+"WorkDetails.project,WorkDetails.workDescription"+"FROM Information LEFT JOIN WorkForce" +
               " ON WorkForce.TInfo_id=Information.ID LEFT JOIN WorkDetails ON WorkDetails.Twf_id=WorkForce.ID1 WHERE Information.Name =?");

      //  if (c != null) {
        //    c.moveToFirst();
       // }
       // return c;

    }
}


编译器给我无法解决rawQuery错误。我一直在寻找答案很长时间,但仍然无法解决。不确定错误是否来自查询问题。任何建议将不胜感激。

最佳答案

尝试这个

     public Cursor readEntry(String name) {

           return database.rawQuery("SELECT i.Weather, i.Date, i.Status, w.SubContractors, w.NumberOfPerson, w.NumberOfHours,
wd.Project, wd.WorkDescription FROM Information i LEFT JOIN WorkForce w
ON w.TInfo_id = i.ID LEFT JOIN WorkDetails wd ON wd.Twf_id = w.ID WHERE
 i.Name = ?",new String[] {String.valueOf(name)},null);

        }


希望能帮助到你 :)

09-30 00:01