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:

enter image description here Please, I need help.

+3


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


source







All Articles