Collect all rows from different tables with true/false value depending on tables they came from-CodePudding

If I have these different tables:

Participants_1
name   | age
James  | 18

Participants_2
name   | age
Daniel | 20
James  | 18

How can I insert it to another table like this?

All_Participants
name   | age | in_participants_1 | in_participants_2
James  | 18  | 1                 | 1
Daniel | 20  | 0                 | 1

So basically the All_Participants tables is the collection of all rows from other Participants tables with additional fields of is_participants_* if they came from specific tables.

CodePudding user response：

You need a UNION ALL with GROUP BY:

-- INSERT INTO ...
SELECT name, age, MAX(in_participants_1), MAX(in_participants_2)
FROM (
  SELECT name, age, 1 AS in_participants_1, 0 AS in_participants_2 FROM participants_1
  UNION ALL
  SELECT name, age, 0,                      1                      FROM participants_2
) AS u
GROUP BY name, age

Ideally you would store all participants in one table and store their ids in participants 1 and 2 table. Or better, create one participation table that stores participant id and event id.