SQL连接 - 用作另一个表中两列的ID的一列 [英] SQL join - one column serving as ID for two columns in another table

问题描述

好吧，也许我已经绝对了解了这个背后的思维过程，我需要放在我的位置，或者说我可能不远。

Okay, maybe I've absolutely goofed on the thought process behind this and I need put in my place, or maybe I'm not far off.

我有一个名为TEAMS的表与两列：teamID和teamName。我还有另一个表WEEK12，其中有三列：gameID，homeID和awayID。

I have one table called TEAMS with two columns: teamID and teamName. I then have another table called WEEK12 with three columns: gameID, homeID and awayID.

我以为也许我可以在homeID和awayID列中使用teamID在WEEK12表中然后使用TEAMS表将其与团队名称匹配。不幸的是，我没有任何运气。我可以加入并获得与homeID或awayID匹配的团队名称，但是我不能同时使用。

I thought maybe I could use the teamID in the homeID and awayID columns for the WEEK12 table and then join that with the TEAMS table to match those two columns up with the team names. Unfortunately, I'm not having any luck. I can join and get team names to match with homeID or awayID, but I can't do both.

任何帮助都非常感谢！

推荐答案

SELECT w.gameID,
       h.teamName AS 'Home Team',
       a.teamName AS 'Away Team' 
FROM WEEK12 AS w 
     LEFT JOIN TEAMS AS h 
               ON w.homeID=h.teamID 
     LEFT JOIN TEAMS AS a 
               ON w.awayID=a.teamID

这篇关于SQL连接 - 用作另一个表中两列的ID的一列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！