mercredi 22 avril 2015

SQLite elements in CREATE VIEW duplicating

This is my view which puts together columns from 3 tables

        db.execSQL("CREATE VIEW " + viewComps +
            " AS SELECT " + COMPANY + "." + colCompID + " AS _id," +
            " " + ACCOUNTS + "." + colName + "," +
            " " + COMPANY + "." + colCompClass + "," +
            " " + PAYMENTS + "." + colGroupID + "," +
            " " + PAYMENTS + "." + colPayDue + "," +
            " " + PAYMENTS + "." + colDateDue + "" +
            " FROM " + PAYMENTS + ", " + COMPANY +
            " JOIN " + ACCOUNTS + " ON " + PAYMENTS + "." + colGroupID + " = " + ACCOUNTS + "." + colID );

PROBLEM

  1. I have 3 companies A, B and C
  2. Acc 1 is assigned to A, 1 Acc = 1 Company ONLY
  3. Acc 1 is inserted and assigned to A, problem is even if I assign it to A it will be duplicated for B and C as well.

The Result:

  Acc 1 | Company A | Payment | Date
  Acc 1 | Company B | Payment | Date
  Acc 1 | Company C | Payment | Date

What it should be :

  Acc 1 | Company A | Payment | Date

Every other account I insert into the database does the same and results in my companies containing a duplicate of all the accounts irregardless of which company the account is assigned to.

QUESTION

What am I doing wrong with my VIEW? I don't understand why it duplicates all the entries putting a copy of each account in each company. Can someone show me where my mistake is? I'm fairly new to this and could use a few pointers in this area. I'm fairly confident it's just a problem with this view as I have another activity that displays accounts based on company and it all works fine there at least.

I'll leave the 3 tables the VIEW references down below in case it's needed:

    db.execSQL("CREATE TABLE " + COMPANY + " (" + colCompID + " INTEGER PRIMARY KEY AUTOINCREMENT, " +
            colCompClass + " TEXT)");

    db.execSQL("CREATE TABLE " + ACCOUNTS + " (" + colID + " INTEGER PRIMARY KEY AUTOINCREMENT, " +
            colName + " TEXT, " +
            colComp + " INTEGER NOT NULL," +
            colAmount + " INTEGER, " +
            colPurpose + " TEXT, " +
            colTerms + " INTEGER NOT NULL, " +
            colPeriod + " INTEGER NOT NULL, " +
            colBalance + " INTEGER, "+
            colStatus + " INTEGER NOT NULL," +
            colDate + " TEXT, " +
            colEditDate + " TEXT, " +
            colRemarks + " TEXT, " +
            "FOREIGN KEY (" + colComp + ") REFERENCES " + COMPANY + " (" + colCompID + " )" + "ON DELETE CASCADE," +
            "FOREIGN KEY (" + colTerms + ") REFERENCES " + TERMS + " (" + colTermsID + " )" + "ON DELETE CASCADE," +
            "FOREIGN KEY (" + colPeriod + ") REFERENCES " + PERIODS + " (" + colPeriodID + ") " + "ON DELETE CASCADE,"+
            "FOREIGN KEY (" + colStatus + ") REFERENCES " + STATUS + " (" + colStatusID + ") ON DELETE CASCADE);");

    db.execSQL("CREATE TABLE " + PAYMENTS + " (" + colPayID + " INTEGER PRIMARY KEY AUTOINCREMENT, " +
            colGroupID + " INTEGER NOT NULL, " +
            colPayBal + " TEXT, " +
            colInterest + " TEXT, " +
            colPayDue + " TEXT, " +
            colDateDue + " TEXT, " +
            colPayDate + " TEXT, " +
            "FOREIGN KEY (" + colGroupID + ") REFERENCES " + ACCOUNTS + " (" + colID + ") ON DELETE CASCADE);");

Aucun commentaire:

Enregistrer un commentaire