I've been having trouble understanding this example, I have the answer but I'm having trouble wrapping my head around how this works. How are the joins working exactly?
Examine the structures of the PLAYER and TEAM tables:
PLAYER
-------------
PLAYER_ID NUMBER(9) PK
LAST_NAME VARCHAR2(25)
FIRST_NAME VARCHAR2(25)
TEAM_ID NUMBER
MANAGER_ID NUMBER(9)
TEAM
----------
TEAM_ID NUMBER PK
TEAM_NAME VARCHAR2(30)
For this example, team managers are also players, and the MANAGER_ID column references the PLAYER_ID column. For players who are managers, MANAGER_ID is NULL.
Which SELECT statement will provide a list of all players, including the player's name, the team name, and the player's manager's name?
ANSWER:
SELECT p.last_name, p.first_name, m.last_name, m.first_name, t.team_name
FROM player p
LEFT OUTER JOIN player m ON (p.manager_id = m.player_id)
LEFT OUTER JOIN team t ON (p.team_id = t.team_id);
So the first LEFT OUTER JOIN takes the player table, then adds on the info for each players manager. Each player has an ID for its manager, who is also a player with an ID. If Mr. A, with player_id 9, is a manager for Ted, with player_id 5, then Ted's manager_id will be 9. The first join takes Ted's manager_id, 9, and matches it to the player_id of his manager, Mr. A, so that the manager's info is now on the table as well, and m.last_name and m.first_name will show Mr. A's name. The second join takes the team_id and simply matches it to the table of teams, appending the team_name to the player's information in the table.
It's tough to explain without sample data and diagrams. Sorry.
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments