3 Tables into 1-CodePudding

I have 3 tables in my database, looking something like this:

Main table

Team ID	Name ID
Set ID 1	Name ID 1
Set ID 1	Name ID 2
Set ID 1	Name ID 3
Set ID 2	Name ID 1
Set ID 2	Name ID 2

Name Table

Name ID	Name
Name ID 1	(insert random name)
Name ID 2	(insert more gibberish)

Team Table

Team ID	Name
Team ID 1	(insert random set name)
Team ID 2	(insert some more gibberish)

Basically I'm trying to replace the main table with the names instead of the Ids, something like this:

Team	Name
Avengers	Captain America
Avengers	Spiderman
Avengers	Hawkeye
Sinister 6	Electro
Sinister 6	Mysterio

I'm using the following code but doesn't seem to want to work

select name.name, team.name
from main_table main
inner join team_table team
on main.team_id = team.team_id
inner join name_table name
on main.name_id = name.name_id

Anything I'm doing wrong? :(

CodePudding user response：

The query works just fine. The order has to be defined as resulsets are unsorted by nature

CREATE TABLE Main_table (
  `Team ID` INTEGER,
  `Name ID` INTEGER
);

INSERT INTO Main_table
  (`Team ID`, `Name ID`)
VALUES
  ('1', '1'),
  ('1', '2'),
  ('1', '3'),
  ('2', '1'),
  ('2', '2');

CREATE TABLE Name_Table (
  `Name ID` INTEGER,
  `Name` VARCHAR(5)
);

INSERT INTO Name_Table
  (`Name ID`, `Name`)
VALUES
  ('1', 'name1'),
  ('2', 'name2'),
  ('3', 'name3'),
  ('4', 'name4');

CREATE TABLE Team_Table (
  `Team ID` INTEGER,
  `Name` VARCHAR(5)
);

INSERT INTO Team_Table
  (`Team ID`, `Name`)
VALUES
  ('1', 'team1'),
  ('2', 'team2');

SELECT t.Name,n.Name 
FROM Main_table m INNER JOIN Name_Table n ON n.`Name ID` = m.`Name ID` INNER JOIN Team_Table t ON t.`Team ID` = m.`Team ID`
ORDER BY t.name,n.Name

Name  | Name 
:---- | :----
team1 | name1
team1 | name2
team1 | name3
team2 | name1
team2 | name2

db<>fiddle here