Dan Rivers Dan Rivers - 4 months ago 10
SQL Question

SQL JOIN - two teams, one logo database

I have two databases, one database(Games) contains hometeamID and visitorID. And the other database(Teams) contains all the logoNames and the teamID. (Games) hometeamID and visitorID are related to (Teams) by teamID.

I am wanting to run a SQl query that outputs the teams from the (Games) database but also shows the logos for each respective team. I want the output to look something like this.

<item>
<hometeamID>
<home_logo>
<visitID>
<visitor_logo>
</item>


When attempting to query using PHP, i use

SELECT *
FROM Games LEFT JOIN
Teams
on Games.homeID = Teams.teamID


I can get the first logo for hometeam fine, but when i use AND

SELECT *
FROM Games LEFT JOIN
Teams
on Games.homeID = Teams.teamID and Games.vistorID = Teams.teamID


I get nothing.

I am able to get the query to work, but i don't get the logos.

Answer

Let's look at the second statement:

SELECT * FROM Games LEFT JOIN Teams on Games.homeID = Teams.teamID and Games.vistorID = Teams.teamID

You are correctly joining the two tables to get the information you need. However, with the AND statement you cause homeID = teamID AND teamID = visitorID. Therefore, we can rewrite this to say homeID = teamID = visitorID. This will never happen because homeID and visitorID will never be to same for a given entry.

In order to get the two sets of logos we need to do two joins in order to add the columns. So, something like:

SELECT Games.homeID, HomeTeam.logo as homeLogo,
       Games.visitorID, VisitorTeam.logo as visitorLogo
FROM Games LEFT JOIN Teams HomeTeam
    ON Games.homeID = HomeTeam.teamID
LEFT JOIN Teams VisitorTeam
    ON Games.visitorID = VisitorTeam.teamID;
Comments