Select operator for multiple foreign keys
I have the following tables:
Livestream
----------
id_livestream int primary key,
name_match varchar(255),
date_match varchar(255),
time_match varchar(255),
league_type int,
tour varchar(255),
stadium int,
id_team1 int,
id_team2 int,
live_video varchar(255),
Team
------
id_team int primary key,
name_team varchar(255),
image_team varchar(255)
League
------
id_league int primary key,
name_league varchar(255)
staduim
-------
id_stadium int primary key,
name_stadium varchar(255)
I am using this sql query to get data from tables as shown below:
select id_livestream,name_match,time_match,date_match,name_league,tour,name_stadium,
live_video
from Livestream,League,staduim,IsLive
where Livestream.league_type=League.id_league and Livestream.stadium=staduim.id_stadium
What I get:
id_livestream|name_match|date_match|time_match|name_league|tour|stadium
65 BarcaMatch 9/5/2017 20:45 League1 22 CampNou
This request is going well, but I don't know how to change this request:
select the command name, image_team from Team for and id_team1, id_team2 from Livestream
UPDATED: I searched and found this query to get what I want, but I cannot add it to my first query:
SELECT
lm.id_team1,
t1.name_team AS name_team_1,
t1.image_team AS image_team_1,
lm.id_team2,
t2.name_team AS name_team_2,
t2.image_team AS image_team_2
FROM Livestream lm
INNER JOIN Team t1
ON lm.id_team1 = t1.id_team
INNER JOIN Team t2
ON lm.id_team2 = t2.id_team
UPDATE 2:
Please, I need help.
+3
Mahdi Hraybi
source
to share
1 answer
Just rewrite the whole query in the explizite join. Format:
SELECT
lm.id_team1,
t1.name_team AS name_team_1,
t1.image_team AS image_team_1,
lm.id_team2,
t2.name_team AS name_team_2,
t2.image_team AS image_team_2,
id_livestream,name_match,time_match,date_match,name_league,tour,name_stadium,
live_video
FROM Livestream lm
INNER JOIN Team t1
ON lm.id_team1 = t1.id_team
INNER JOIN Team t2
ON lm.id_team2 = t2.id_team
JOIN League on lm.league_type=League.league
JOIN staduim on lm.stadium=staduim.id_stadium
+1
Jens
source
to share