CODEWITHSUNDEEP
sqlpostgresql
kira_codes
kira_codes

Reputation: 1616

SQL cross-reference table self-reference

I am working on a project where I have a table of 

all_names(
  team_name TEXT,
  member_name TEXT,
  member_start INT,
  member_end INT);

What I have been tasked with is creating a table of 

participants(
  ID SERIAL PRIMARY KEY,
  type TEXT,
  name TEXT);

which contains all team and member names as their own entries. Type may be either "team" or "member".

To compliment this table of participants I am trying to create a cross-reference table that allows a member to be referenced to a team by ID and vice versa. My table looks like this: 

belongs_to(
  member_id INT REFERENCES participants(ID),
  group_id INT REFERENCES  participants(ID),
  begin_year INT,
  end_year INT,
  PRIMARY KEY (member_id, group_id);

I am unsure of how to proceed and populate the table properly.

The select query I have so far is:

SELECT DISTINCT ON (member_name, team_name)
  id, member_name, team_name, member_begin_year, member_end_year
FROM all_names
INNER JOIN artists ON all_names.member_name = participants.name;

but I am unsure of how to proceed. What is the proper way to populate the cross reference table?

Upvotes: 1

Views: 645

Answers (1)

Patrick
Patrick

Reputation: 32326

Probably the easiest solution is to use a few statements. Wrap this is a transaction to make sure you don't get concurrency issues:

BEGIN;

INSERT INTO participants (type, name)
  SELECT DISTINCT 'team', team_name
  FROM all_names
  UNION
  SELECT DISTINCT 'member', member_name
  FROM all_names;

INSERT INTO belongs_to 
  SELECT m.id, g.id, a.member_start, a.member_end
  FROM all_names a
  JOIN participants m ON m.name = a.member_name
  JOIN participants g ON g.name = a.team_name;

COMMIT;

Members that are part of multiple teams get all of their memberships recorded.

Upvotes: 2

Related Questions