Join tables in sqlite with many to many

Question

I have the following database schema:

create table people (
    id integer primary key autoincrement,
);

create table groups (
    id integer primary key autoincrement,
);

and I already have which people are members of which groups in a separate file (let's say in tuples of (person id, group id). How can I structure my database schema such that it's easy to access a person's groups, and also easy to access the members of a group? It is difficult and slow to read the tuples that I currently have, so I want this to be in database form. I can't have things like member1, member2, etc. as columns because the number of people in a group is currently unlimited.

Answer 1

Move your text file into a database table

CREATE TABLE groups_people (
  groups_id integer,
  people_id integer,
  PRIMARY KEY(group_id, people_id)
);  

And select all people that are a member of group 7

SELECT * FROM people p  
  LEFT JOIN groups_people gp ON gp.people_id = p.id  
  WHERE gp.groups_id = '7';

And select all the groups that person 5 is in

SELECT * FROM groups g  
  LEFT JOIN groups_people gp ON gp.groups_id = g.id  
  WHERE gp.people_id = '5';