如何从子表中回拨父母姓名？

Question

I have two tables as follows:

games

id | game_name | console_id
    1     God of War     1
    2     Zelda          3
    3     Sonic          4

consoles

id | console_name 
1    PS4
2    Xbox
3    Switch
4    Mega Drive

I've linked them via the Designer view so when I go to insert a name game (I'm doing this via the phpMyAdmin control panel) as soon as I click on "console_id" it gives me a drop down of 1 - PS4, 2 - Xbox and so on. So the games table can now read from consoles table no problem so I think I've got everything correct from that side of things.

Now I can run this code

SELECT id, game_name, console_id FROM games

and I will get the output

1 - God of war - 1

What I would like to do is something like this:

SELECT id, game_name, console_id.console_name FROM games

So instead of it saying God of war is on console ID 1, it says its on console.name PS4.

How can I do it, as I thought by linking the tables it would allow me to do it.

Answer 1

You need a JOIN between the tables. In this case, you want to join the consoles table to the games table by matching the id field of the former to the console_id field of the latter, like this:

SELECT g.id, g.game_name, c.console_name
FROM games g
INNER JOIN consoles c
  ON c.id = g.console_id