使用1 JOIN和另一个LEFT JOIN连接2表

I am currently using MySQL and I need to select from 3 tables. I have 3 table: Game, Game_genre and comments.

我目前正在使用MySQL，我需要从3个表中进行选择。我有3个表：游戏，Game_genre和评论。

游戏表中GameID是PK

The Game_genre table where GameID and GenreID is the PK

Game_genre表，其中GameID和GenreID是PK

What I have tried is

我试过的是

SELECT G.GameTitle, G.Company, G.ReleaseDate, G.Description, G.Price, GG.GenreName, G.Preowned, G.ImagePath, round((G.Price*0.8),2)'SalesPrice'
from game G, game_genre GG, comments C
LEFT JOIN C ON G.GameID
LEFT JOIN GG on G.GameID
WHERE G.GameID = GG.GameID;

However, this comes out with a error of 1066 even though I have given each table an alias

但是，即使我给每个表一个别名，这也会出现1066的错误

3 个解决方案

#1

When you're using JOIN clauses you don't list all the tables in the FROM clause, only the first table goes there. And the relations between the tables go in the ON clause, not the WHERE clause.

当您使用JOIN子句时，您没有列出FROM子句中的所有表，只有第一个表到那里。表之间的关系在ON子句中，而不是WHERE子句。

SELECT G.GameTitle, G.Company, G.ReleaseDate, G.Description, G.Price, GG.GenreName, G.Preowned, G.ImagePath, round((G.Price*0.8),2)'SalesPrice'
FROM game G
LEFT JOIN C ON G.GameID = C.GameID
LEFT JOIN GG ON G.GameID = GG.GameID;

Please review your SQL textbook or tutorial to learn the basic syntax.

请查看您的SQL教科书或教程以了解基本语法。

#2

The proper syntax is:

正确的语法是：

SELECT G.GameTitle, G.Company, G.ReleaseDate, G.Description, G.Price,
       GG.GenreName, G.Preowned, G.ImagePath,
       round((G.Price*0.8),2) as SalesPrice
FROM game G LEFT JOIN
     comments C
     ON G.GameID = C.GameId LEFT JOIN
     game_genre GG 
     on G.GameId = GG.GameId;

I am guessing that the join on Game_Genre is on something like the GenreId; however, your code has it on GameId.

我猜测Game_Genre上的加入是像GenreId这样的东西;但是，你的代码在GameId上有它。

Also, don't use single quotes for column aliases. Only use single quotes for string and date constants.

另外，不要对列别名使用单引号。仅对字符串和日期常量使用单引号。

#3

Just try like below:

试试如下：

SELECT G.GameTitle, G.Company, G.ReleaseDate, G.Description, G.Price, GG.GenreName, G.Preowned, G.ImagePath, round((G.Price*0.8),2)'SalesPrice' from game G LEFT JOIN game_genre GG ON G.GameID = GG.GameID LEFT JOIN comments C ON G.GameID = C.GameID WHERE <your condition>

SELECT G.GameTitle，G.Company，G.ReleaseDate，G.Description，G.Price，GG.GenreName，G.Preowned，G.ImagePath，round（（G.Price * 0.8），2）来自游戏的'SalesPrice' G LEFT JOIN game_genre GG ON G.GameID = GG.GameID LEFT JOIN评论C ON G.GameID = C.GameID WHERE <你的情况>

I would suggest you to learn a bit more about JOINS of SQL syntax :)

我建议你学习更多关于SQL语法的JOINS :)

#1