Conditional queries
Having a little problem with this question: Using the two tables below, create a subquery or join operator where player, team and is not in the Player_PROFILE table for all the players trained by Fischer.
I came up with the following SQL code, but I'm not sure how to end it or if it's even correct:
SELECT PLAYER_PROFILE.Player, PLAYER_PROFILE.Team,
PLAYER_PROFILE.No
FROM PLAYER_PROFILE
INNER JOIN PLAYER_DETAIL
ON......
source to share
SELECT PLAYER_PROFILE.Player, PLAYER_PROFILE.Team,
PLAYER_PROFILE.No
FROM PLAYER_PROFILE
INNER JOIN PLAYER_DETAIL
ON PLAYER_PROFILE.Player = PLAYER_DETAIL.Player
WHERE PLAYER_DETAIL.Coach = "Fisher"
You are doing an inner join ( http://www.w3schools.com/sql/sql_join_inner.asp ) on the player's name because you need some piece of data that is unique to each player ... Usually it will be some kind of id (number / guid), but in the case of your data, this is the player's name.
source to share
Since both tables only have a Player
common field, so the query would look like this:
SELECT PLAYER_PROFILE.Player, PLAYER_PROFILE.Team,
PLAYER_PROFILE.No
FROM PLAYER_PROFILE
INNER JOIN PLAYER_DETAIL
ON PLAYER_PROFILE.Player = PLAYER_DETAIL.Player
WHERE PLAYER_DETAIL.Coach = 'Fisher'
Explanation: The above query will concatenate the results of both tables based on name Player
and will filter the result when Coach
Fisher
If you could change the structure of the table, I would suggest you have a field PLAERY_ID
in both tables to uniquely identify the player instead of the column Player
.
source to share
SELECT
`PLAYER_PROFILE`.`Player`,
`PLAYER_PROFILE`.`Team`,
`PLAYER_PROFILE`.`No`
FROM
`PLAYER_PROFILE`
INNER JOIN
`PLAYER_DETAIL`
ON
`PLAYER_DETAIL`.`Player`=`PLAYER_PROFILE`.`Player`
WHERE
`PLAYER_DETAIL`.`Coach`="Fisher";
Different players may have the same names, so it would be better to use a unique ID for each player and join those values.
source to share