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 ) ) I try to use Aggregate and use join beetwen two table with condition and its normally. On my case I should get same data from two table and groupped some column. Contest table points either to Team or Person table depending on the participant type: Joining two table on different database Question Aggregate External Database Traditional Web Application Type Traditional Web Dear All, I work with external database. 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.
0 kommentar(er)
