如何根据多表选择SQL结果 [英] How to select SQL results based on multiple tables

问题描述

我需要根据其他几个表中的某些匹配值从一个表中选择结果.我有以下表格:

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?

推荐答案

这会给你教练:

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

这将为您提供球员:

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

因此，不优雅、简单的答案是将它们与 UNION 一起折腾.

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

这篇关于如何根据多表选择SQL结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！