CODEWITHSUNDEEP
sql
LukeDS
LukeDS

Reputation: 141

sql select same field twice for the same row

There are two tables one for competition another for teams.

Competition table contains information about the competition, scores, date, and teamIds. (teamsId is important)

| Competition | Team1 | Team2 | date
|      5      |  22   |   11  | 01-01-98|

Team table contains information about each team, including the team's name. (Teams Name is important)

| team Id | TeamName |
|    22   |Barcelona |
|    11   |Manchester|

Query:

SELECT competition, team1, team2, date, TeamName ta, TeamName as tb
FROM comp 
INNER JOIN teamname ON (team1 = Teamname)
WHERE team1 = 22 Limit 1;

So far i get this:

| Competition | Team1 | Team2 | date    |ta        |   tb    |  
|      5      |  22   |   11  | 01-01-98|Barcelona |Barcelona|

I need this result

| Competition | Team1 | Team2 | date    |ta        |tb       |
|      5      |  22   |   11  | 01-01-98|Manchester|Barcelona|

How can I return one team name to team1name and the other team name to team2name? without having more than one row.

I would prefer if I don't have to add any new select or union all, as this is just a small piece of the actual query, and on the original query, there is quite a few select and union all already.

Upvotes: 0

Views: 131

Answers (2)

Zaynul Abadin Tuhin
Zaynul Abadin Tuhin

Reputation: 32021

use join teamname twice 

 select t3.Competition,t3.Team1,t3.Team2,t3.TeamName as team1,t2.TeamName as team2
from
(
    SELECT c.*,t1.TeamName

    FROM comp c
    left JOIN team t1 ON t1.teamid = c.Team1
) t3 left join team t2 ON t3.Team2 = t2.teamid


  Competition   Team1   Team2   team1          team2
           5     22          11     Barcelona   Manchester

Upvotes: 0

jarlh
jarlh

Reputation: 44805

Simply JOIN twice!

SELECT competition, team1, team2, date, t1.TeamName ta, t2.TeamName as tb
FROM comp 
INNER JOIN team t1 ON (team1 = t1.teamid)
INNER JOIN team t2 ON (team2 = t2.teamid)
WHERE team1 = 22 Limit 1;

Upvotes: 1

Related Questions