0

I have read many answers on this question but I still can not solve the problem :(
These are my tables.

public static final String DATABASE_NAME = "EventBoard.db";
public static final String EVENTS_TABLE = "Events";
public static final String GUESTS_TABLE = "Guests";
public static final String LINKER_TABLE = "Linker";

These are their fields.

  @Override
  public void onCreate(SQLiteDatabase db) {
    db.execSQL("create table " + EVENTS_TABLE + "( _id INTEGER PRIMARY KEY AUTOINCREMENT,Name TEXT,Time TEXT,Date TEXT,Venue TEXT,Contact TEXT,Description TEXT);");
    db.execSQL("create table " + GUESTS_TABLE + "( _id INTEGER PRIMARY KEY AUTOINCREMENT,Name TEXT,Phone TEXT unique);");
    db.execSQL("create table " + LINKER_TABLE + "( _id INTEGER PRIMARY KEY AUTOINCREMENT,Guests TEXT);");
  }

What I want to do is get all the guests from name column in my guest table to the Guests column in my Linker Table. I have read a lot of other similar questions but it simply did not work. The Linker table will allow me to form a table between Guests(as rows) and Events(as columns) so I can store a guest list for each event.

 public void updateLinkerTable(){
    SQLiteDatabase db = this.getWritableDatabase();
    db.rawQuery("",null);
}

Above is how I am sending a query to the database. Any help would be appreciated. I'm stuck on this problem since 2 days now.

INSERT INTO Linker (Guests) SELECT Name FROM Guests

When I run this SQL Query in my SQLite Manager Mozilla Plugin it runs fine and brings all the names from Guests table to Guests in Linker table..But when I run the same query in my Android code it does not work and I dont even get any error related to it.

user6633315
  • 15
  • 5
  • You shouldn't duplicate data. You should use foreign keys. – OneCricketeer Jul 28 '16 at 03:47
  • Thanks for editing, now as stated, your schema should be `(Event_ID, Guest_ID)`, not the text of the Guest. You can JOIN on ID later to get Guest information – OneCricketeer Jul 28 '16 at 04:17
  • @cricket_007 http://stackoverflow.com/questions/38559909/how-do-i-use-struct-arrays-or-similar-data-structures-in-android-sqlite This is my original problem and my ultimate goal. It will help you understand what I really am trying to do. – user6633315 Jul 28 '16 at 04:19
  • I understand what you want just fine. I'm giving you the idea of how to implement it. I'm just not available to give a full answer right now – OneCricketeer Jul 28 '16 at 04:25
  • db.rawQuery("SELECT _id, _id FROM Events CROSS JOIN Guests",null); This is what I am trying right now. How do I put the result of this JOIN in my Linker table? – user6633315 Jul 28 '16 at 04:47
  • Nope cross join in less efficient. Say you want all guests for an event X. You would do `SELECT l.event_id, * FROM Linker l JOIN Guests g ON l.guest_id = g._id WHERE l.event_id = X`. But to do that, you need ID data in the linker table and foreign key references – OneCricketeer Jul 28 '16 at 05:09
  • I am fairly new to SQLite. So I still want to make a table of EventNames * GuestIDs... I understand its not the best way but at least it is the way for me to understand easily and work on it for now. I will try to improve it as I learn more about Android and SQLite :) – user6633315 Jul 28 '16 at 05:41
  • 'db.execSQL("create Table Linker AS SELECT (Name) from Guests;");' This is how I managed to get the GuestNames into the Linker Table finally – user6633315 Jul 28 '16 at 05:46
  • That is absolutely not how I would imagine how you want to use the linker table. When a guest joins an event, you want to insert only one row of (event, guest) into that table. You don't want to insert all the guests into that table... – OneCricketeer Jul 28 '16 at 06:00
  • I think i understand your model now. But how would I eliminate multiple entries in that case? e.g (event 1 - guest 3) , (event 2- guest 3) , (event 1 - guest 3).. I can set event ID to unique but not the guest IDs as they can be repeated. So it would become necessary to check if such a record already exists. Although, your model would require minimum storage memory but would it not increase computation? – user6633315 Jul 28 '16 at 06:10
  • Guest 3 can't attend Event 1 more than once... So there should be no duplicates. It has less computation than a CROSS JOIN if you use an INNER JOIN as I wrote earlier – OneCricketeer Jul 28 '16 at 06:14
  • Your solution was much better. I'm sorry I just did not understand SQL well enough. INNER JOIN is infact the best solution. Linker table would only consist of GUEST_ID and EVENT_ID and then I can get my result using the INNER JOIN and display my data accordingly. Thanks a lot for your help :) – user6633315 Jul 29 '16 at 10:02
  • Welcome. Sorry I wasn't able to give a complete Android code answer – OneCricketeer Jul 29 '16 at 12:39

0 Answers0