CODEWITHSUNDEEP
androidmysqlsqlitesyntax
Cai
Cai

Reputation: 5323

SQLITE CREATE VIEW Syntax issues with JOIN

Having a bit of trouble in CREATE VIEW with JOIN when I try to join 3 tables together, I can do 2 but 3 seems to be a no go for me. I'm new to this so I'm just going by trial and error since most of the documentations I've gone through didn't really cover ( to my understanding ) joining 3 tables and it's not really going well so here I am for some advice and hopefully a solution. Thanks in advance.

Here are the tables:

Terms

db.execSQL("CREATE TABLE " + termsTable + " (" + colTermsID + " INTEGER PRIMARY KEY , " + colTermsClass + " TEXT)");

Status

  db.execSQL("CREATE TABLE " + statTable + " (" + colStatusID + " INTEGER PRIMARY KEY , " + colStatClass + " TEXT)");

Accounts

db.execSQL("CREATE TABLE " + accountsTable + " (" + colID + " INTEGER PRIMARY KEY AUTOINCREMENT, " +
            colName + " TEXT, " +
            colAmount + " Integer, " +
            colPurpose + " TEXT, " +
            colTerms +" INTEGER NOT NULL, " +
            colStatus +" INTEGER DEFAULT '1'," +
            colDate + " TEXT, " +
            "FOREIGN KEY (" + colTerms + ") REFERENCES " + termsTable + " (" + colTermsID + ") " + "," +
            "FOREIGN KEY (" + colStatus + ") REFERENCES " + statTable + " (" + colStatusID + "));");

This is the CREATE VIEW I'm using which works fine with just one of either Terms or Status but not when I try to use both so I assume I'm doing something wrong in that area.

    db.execSQL("CREATE VIEW " + viewAccs +
                    " AS SELECT " + accountsTable + "." + colID + " AS _id," +
                    " " + accountsTable + "." + colName + "," +
                    " " + accountsTable + "." + colAmount + "," +
                    " " + accountsTable + "." + colPurpose + "," +
                    " " + termsTable + "." + colTermsClass + "" +
                    " FROM " + accountsTable + " JOIN " + termsTable + " ON " + accountsTable + "." + colTerms + " = " + termsTable + "." + colTermsID +
                    " " + statTable + "." + colStatClass + "" +
                    " FROM " + accountsTable+ " JOIN " + statTable + " ON " + accountsTable + "." + colStatus + " = " + statTable + "." + colStatusID +
                    " " + accountsTable + "." + colDate
    );

EDIT ( ANSWER HERE ):

This is the actual code for future reference I ended up with after fixing the Syntax and following what X.L Ant mentioned.

            db.execSQL("CREATE VIEW " + viewAccs +
                    " AS SELECT " + accountsTable + "." + colID + " AS _id," +
                    " " + accountsTable + "." + colName + "," +
                    " " + accountsTable + "." + colAmount + "," +
                    " " + accountsTable + "." + colPurpose + "," +
                    " " + termsTable + "." + colTermsClass + "," +
                    " " + statTable + "." + colStatClass + "," +
                    " " + accountsTable + "." + colDate + "" +
                    " FROM " + accountsTable +
                    " JOIN " + termsTable + " ON " + accountsTable + "." + colTerms + " = " + termsTable + "." + colTermsID +
                    " JOIN " + statTable + " ON " + accountsTable + "." + colStatus + " = " + statTable + "." + colStatusID
    );

Upvotes: 0

Views: 130

Answers (1)

xlecoustillier
xlecoustillier

Reputation: 16351

You can't have two FROM in one query.

Try this:

db.execSQL("CREATE VIEW " + viewAccs +
                " AS SELECT " + accountsTable + "." + colID + " AS _id," +
                " " + accountsTable + "." + colName + "," +
                " " + accountsTable + "." + colAmount + "," +
                " " + accountsTable + "." + colPurpose + "," +
                " " + termsTable + "." + colTermsClass + "" +
                " FROM " + accountsTable + 
                " JOIN " + termsTable + " ON " + accountsTable + "." + colTerms + " = " + termsTable + "." + colTermsID +
                " " + statTable + "." + colStatClass + "" +
                " JOIN " + statTable + " ON " + accountsTable + "." + colStatus + " = " + statTable + "." + colStatusID +
                " " + accountsTable + "." + colDate

Make sure the variables you're injecting in your SQL are safe to avoid injection.

Upvotes: 0

Related Questions