MySQL - How to Join Different Tables Based on Condition (Switch Join - Select Tables on Condition)

Sometimes in a single query, it is required to join different tables based on a condition in one of the tables.

For example, you need to get all persons participating in a contest as individuals or as members of a team. Contest table points either to Team or Person table depending on the participant type:

SQL:

  -- Persons who can participate as individuals or team members
  CREATE TABLE persons (id INT, name VARCHAR(30));
 
  -- Persons who participate as a team
  CREATE TABLE teamPersons (team_id INT, person_id INT);
 
  -- Type 'P' points to Person, type 'T' to teamPersons (team_id)
  CREATE TABLE contestParticipants (participant_id INT, participant_type CHAR);

Sample Data:

   -- There are 3 persons: Dan, Tom and Steve
   INSERT INTO persons VALUES (1, 'Dan'), (2, 'Tom'), (3, 'Steve');
 
   -- Dan and Tom are in Team 1 
   INSERT INTO teamPersons VALUES (1, 1), (1, 2);
 
   -- Team 1 and Steve participate in contest
   INSERT INTO contestParticipants VALUES (1, 'T'), (3, 'P');

So how to get all persons participating in the contest in a single SQL query?

You can use UNION clause to combine the results from several queries into one:

   SELECT p.name
   FROM contestParticipants c, persons p
   WHERE participant_type = 'P' AND participant_id = p.id
   UNION ALL
   SELECT p.name
   FROM contestParticipants c, teamPersons tp, persons p
   WHERE participant_type = 'T' AND participant_id = tp.team_id AND tp.person_id = p.id;

The query returns:

Explanation:

• The first SELECT query selects persons participating as individuals, and it directly joins ContestParticipants and Persons tables
• The second SELECT query selects teams and defines its members. It joins ContestParticipant with TeamPersons and then Persons tables

It is possible to get the same result without UNION:

   SELECT IFNULL(p.name, p2.name)
   FROM contestParticipants c
     LEFT OUTER JOIN persons p ON (c.participant_id = p.id AND c.participant_type = 'P')
     LEFT OUTER JOIN teamPersons tp ON (c.participant_id = tp.team_id AND c.participant_type = 'T')
     LEFT OUTER JOIN persons p2 ON tp.person_id = p2.id;

The query returns:

Explanation:

Firstly you get names of individuals by joining ContestParticipant and Person tables, but you use LEFT OUTER JOIN so Team rows remains:

   SELECT *
   FROM contestParticipants c
     LEFT OUTER JOIN persons p ON (c.participant_id = p.id AND c.participant_type = 'P')

Result:

Then we get ID of persons participating as team members:

   SELECT *
   FROM contestParticipants c
     LEFT OUTER JOIN persons p ON (c.participant_id = p.id AND c.participant_type = 'P')
     LEFT OUTER JOIN teamPersons tp ON (c.participant_id = tp.team_id AND c.participant_type = 'T')

Result:

The we get person names participating as team members:

   SELECT *
   FROM contestParticipants c
     LEFT OUTER JOIN persons p ON (c.participant_id = p.id AND c.participant_type = 'P')
     LEFT OUTER JOIN teamPersons tp ON (c.participant_id = tp.team_id AND c.participant_type = 'T')
     LEFT OUTER JOIN persons p2 ON tp.person_id = p2.id;

Result:

Finally you use IFNULL(p.name, p2.name) to select the person name as an individual or team member.

• Select Top N per Each Group
• Select and Update in Single Statement (Increment Counter avoiding Race Condition)
• Select Rows Depending on Value in Previous Row (Remove Duplicates in Each Sequence)

• MySQL Articles and Reference