CODEWITHSUNDEEP
mysqlsql
Brandon Powell
Brandon Powell

Reputation: 1

Joining three tables on same primary Key MYSQL

I am trying to join three tables on the first tables Primary key, of which both other tables possess a foreign Key.

Example

SELECT *
FROM team_member
INNER JOIN bench_member
ON team_member.team_member_id=bench_member.team_member_id
INNER JOIN player
ON team_member.team_member_id=player.team_member_id

Each join works individually, but when i try to combine them. it gives me empty result sets. Really confused as to what is going on here.

Upvotes: 0

Views: 1939

Answers (2)

BuddingProgrammer
BuddingProgrammer

Reputation: 51

Try Left Outer Joins instead. You will be better able to analyze data.

Upvotes: 0

Roberto
Roberto

Reputation: 533

Assuming that each team_member is either a bench_member or a player and not both, then you might want something like this. SQLFiddle

select t.*, 
    case when b.team_member_id is not null then 'Bench'
    when p.team_member_id is not null then 'Player'
end
from team_member t
left join bench_member b on t.team_member_id = b.team_member_id
left join player p on t.team_member_id = p.team_member_id

Upvotes: 1

Related Questions