CODEWITHSUNDEEP
mysqlsqlsql-server
Jacob
Jacob

Reputation: 3698

SQL Getting Specific Column other than just the foreign key

I have sql table called Games as follows:

CREATE TABLE Games
(date date NOT NULL,
ho_t_id varchar(9) NOT NULL,
v_t_id varchar(9) NOT NULL,
h_score int,
v_score int,

PRIMARY KEY(date, ho_t_id, v_t_id),
FOREIGN KEY(ho_t_id) REFERENCES Team ON UPDATE CASCADE,
FOREIGN KEY(v_t_id) REFERENCES Team);

As you can see, it has two foreign keys to a table called Team. The ho_t_id and v_t_id reference to the primary key of the Team table called t_id.

I would like to show all the columns of the Games table (*) but replace the ho_t_id by the name of the team which exist in the Team table. (The Team table contains a column named Name).

How can I do that?

Upvotes: 0

Views: 27

Answers (2)

Jon Kartago Lamida
Jon Kartago Lamida

Reputation: 854

SELECT G.*, T1.NAME as T1_NAME, T2.NAME AS T2_NAME
FROM GAMES G
INNER JOIN TEAM T1 ON T1.T_ID=G.HO_T_ID
INNER JOIN TEAM T2 ON T2.T_ID=G.V_T_ID

Upvotes: 1

artm
artm

Reputation: 8584

You mean this?:

select t.Name, g.allOtherColumns
from Games g
inner join (
    select Name, ID 
    from Team
) t
ON t.ID = g.v_t_id
--where  ....

Upvotes: 1

Related Questions