I have this code

SELECT
    home, away, teams.name as home_name, teams.abbreviation as home_abbr, teams.name as away_name, teams.abbreviation as away_abbr
FROM
    games
INNER JOIN
    teams ON games.home = teams.id
WHERE
    games.date >= '2022-01-01'

and this result

I have two tables "games" and "teams". In "games" table have home and away columns. There only wrote id team. In "teams" table have name, abbriviation, icon and other information. I need get home and away teams names and abbriviations with union query from "teams" table. In my situation get only one (home or away) column information

CodePudding user response：

The issue is you have two values of teams in the "game" you need to "lookup". This means you need two joins in order to get the different values for home and away.

Assuming

• game has home and away foreign key ID's from teams table...
• each game consists of two teams one "home" and one "away"

We simply join from games twice, once for the "home team" data, and once for the "away team" data. We will need to alias each join "h" (home) "a" (away) and then update fields to use the correct alias.

Giving us:

SELECT g.home, g.away, 
       h.name as home_name, h.abbreviation as home_abbr, 
       a.name as away_name, a.abbreviation as away_abbr
FROM games g
INNER JOIN teams h
  ON g.home = h.id
INNER JOIN teams a
  ON g.away = a.id
WHERE g.date >= '2022-01-01'

Note: For clarity I aliased all 3 tables and each of the fields in select/join/where

Outstanding questions You said, " need get home and away teams names and abbreviations with union query from "teams" table."

Why is a union needed at all? Was this how you thought you could get both teams names/abbreviations? If so this approach doesn't make sense to me as a Join will suffice. unless you're looking for results for a game on different lines: which your example output didn't show. So i think union is the wrong approach given desired results. This isn't to say it couldn't be done with a union and a max and a group by; but it's more work and likely slower than a simple join.