The we get person names participating as team members:įinally you use IFNULL(p.name, p2.name) to select the person name as an individual or team member. participant_type = 'P' ) LEFT OUTER JOIN teamPersons tp ON (c. Then we get ID of persons participating as team members: id įirstly you get names of individuals by joining ContestParticipant and Person tables, but you use LEFT OUTER JOIN so Team rows remains: participant_type = 'T' ) LEFT OUTER JOIN persons p2 ON tp. Because multiple database connections can create temporary tables with the same name, SQL Server. participant_type = 'P' ) LEFT OUTER JOIN teamPersons tp ON (c. This is a unique identifier for the temporary table. It is possible to get the same result without UNION:
#Mysql join databases different servers how to
So how to get all persons participating in the contest in a single SQL query? Team 1 and Steve participate in contest INSERT INTO contestParticipants VALUES ( 1, 'T' ), ( 3, 'P' ) Dan and Tom are in Team 1 INSERT INTO teamPersons VALUES (1, 1 ), (1, 2 ) Sample Data: - There are 3 persons: Dan, Tom and Steve INSERT INTO persons VALUES ( 1, 'Dan' ), ( 2, 'Tom' ), ( 3, 'Steve' ) Type 'P' points to Person, type 'T' to teamPersons (team_id) CREATE TABLE contestParticipants (participant_id INT, participant_type CHAR ) Persons who participate as a team CREATE TABLE teamPersons (team_id INT, person_id INT ) Persons who can participate as individuals or team members CREATE TABLE persons (id INT, name VARCHAR (30 ) ) Contest table points either to Team or Person table depending on the participant type:
Sometimes in a single query, it is required to join different tables based on a condition in one of the tables.įor example, you need to get all persons participating in a contest as individuals or as members of a team. Here are the different types of the JOINs in SQL: (INNER) JOIN: Returns records that have matching values in both tables.
0 kommentar(er)
