foreign keys - How to select SQL results based on multiple tables

Question

I need to select results from one table based on certain matching values in a couple of other tables. I have the following tables:

person: id, firstname, lastname
team: id, teamname
player: id, person_id(FK), team_id(FK)
coach: id, person_id(FK), team_id(FK)

I need to return all the coaches and players names for each team. I've only ever used inner joins, and it doesn't seem like I can use those here, so any idea how to do this?

Answer 1

This will give you the coach:

SELECT team.Teamname, person.Firstname, person.Lastname
FROM person
JOIN coach ON person.id = coach.person_id
JOIN team  ON coach.team_id = team.id

And this will give you the players:

SELECT team.Teamname, person.Firstname, person.Lastname
FROM person
JOIN player ON person.id = player.person_id
JOIN team  ON player.team_id = team.id

So, the non-elegant, simple answer is to just toss it all together with UNION.