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.

Answer 2

Just use an OR in the join to Team

SELECT      P.firstname,      P.lastname,      T.teamname FROM     person p id     LEFT JOIN player pl     ON p.id = pl.person_id     LEFT JOIN coach c     ON p.id = c.person_id     LEFT JOIN team t     ON pl.team_id = t.id          or.c.team_id = t.id 

Or if you perfer if and your database has COALESCE

   LEFT JOIN team t         ON COALESCE(pl.team_id,c.team_id)  = t.id