How to join multiple SQL tables without matching columns and different number of row-CodePudding

I was hoping someone could help or point me in the right direction, I’m pretty new to SQL queries having mainly focused on FE and have the following issue.

I have 4 tables I need to connect and pull information from each:

Users, Squad, Games-played and Games.

The current possible column matches through FKs are:

Users

id

Squads

id
Player
Users.id FK

Games played 

id
Squads.id FK
Games.id FK

Games

id
name
Games_played.id FK

I need to get Games.name from the Games table to the Squad table, Games_played is my middle point and I’m wonder how I can use it to get that information across.

I can pass Games.id through to Squads from it being the FK for Games_played.

Games.id corespondents with Games.name (the column I want),

Whilst Games and Squads have a different number of rows I wanted to display Games.name, which may repeat themselves on Squads.

Example desired output:

Users.id = 1 | Squads.Players = w | Games_played.Games.id = 1 | Games.name = x

Users.id = 2 | Squads.Players = y | Games_played.Games.id = 2 | Games.name = z

Users.id = 3 | Squads.Players = s | Games_played.Games.id = 1 | Games.name = x

So Games_played.Games.id = 1 will always show Games.name = x

Users.id passed to Squads via FK.

Player coming from Squads,

Games.id coming from Games but passed as FK to Games_played and able to join to Squads as Games_played.Games.I’d,

Games.name coming from Games and somehow attached to Squads.

Any help would be greatly appreciated. Thank you.

CodePudding user response：

I need to get Games.name from the Games table to the Squad table

I guess you mean that you want a query the returns the contents of the Squad table, augmented by the Games.name of the associated Game. From the available structure, the natural and only plausible alternative appears to be this:

select
  s.*,
  gp.Games_id,
  g.name
from
  Squads s
    left join Games_played gp on gp.Games_id = s.id
    left join Games g on gp.Games_id = Games.id

If you want to ignore squads that that don't have any associated Games_played then use ordinary inner joins instead of left joins.

Whilst Games and Squads have a different number of rows I wanted to display Games.name, which may repeat themselves on Squads.

That's not inherently problematic, but note well that given your key structure, your result will have one row for every row of Games_played, plus (in the left join case) one row for every squad that is not associated with any Games_played. The latter group will have NULL Games_id and name. That may mean multiple rows for some squads, each with a different Games_id and possibly different names. It could also mean multiple rows for some Games, each with different Squad details.