CODEWITHSUNDEEP
mysql
Mahdi H
Mahdi H

Reputation: 339

Select statement for multiple foreign keys

I have these 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 the data from the tables as 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 am getting :

id_livestream|name_match|date_match|time_match|name_league|tour|stadium

     65       BarcaMatch  9/5/2017   20:45       League1    22  CampNou

This query is going well but i don t know how to alter this query to :

select the name team,image_team from Team for both id_team1,id_team2 from Livestream

UPDATED : i have searched and found this query to get what i want but i can t 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

Upvotes: 3

Views: 124

Answers (1)

Jens
Jens

Reputation: 69440

Simply rewrite the whole query in 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

Upvotes: 1

Related Questions