内部联接上的MySQL列错误

I'm following a tutorial, and want to create a user role view. This is the first thing I've ever done with SQL, though I know some of the basics. I'm using the MySQL Workbench.

我正在学习一个教程,并希望创建一个用户角色视图。这是我用SQL做过的第一件事,虽然我知道一些基础知识。我正在使用MySQL Workbench。

My statement is like this:

我的陈述是这样的:

CREATE VIEW `v_user_role` AS

SELECT  u.Username, u.Password, g.name

 FROM `users_groups` ug

 INNER JOIN `user_table` u ON u.iduser = ug.userid

 INNER JOIN `group` g ON g.idgroup =  ug.groupid;

And the error I'm getting is:

而我得到的错误是:

ERROR 1054: Unknown column 'ug.userid' in 'on clause'

There's a foreign key in the users_groups table with the name userid. What am I doing wrong?

users_groups表中有一个名为userid的外键。我究竟做错了什么?

UPDATE: Describe for users_groups which just shows the id and not the foreign keys. The select query mentioned below fails.

更新:描述只显示id而不是外键的users_groups。下面提到的选择查询失败。

1 个解决方案

#1

Since it's complaining about the users_groups.userid column, your _first step is to check that:

由于它抱怨users_groups.userid列,所以_first步骤是检查:

select userid from users_groups

With the renaming of table and column names, the most likely explanation is that you're renamed something wrongly.

通过重命名表名和列名,最可能的解释是您错误地重命名了。

It would be a lot easier if you supplied the actual schema rather than the tutorial link and, by that, I mean the schema output from MySQL itself with something like:

如果您提供实际的模式而不是教程链接会更容易,并且,我指的是MySQL本身的模式输出,例如:

select * from information_schema.tables where table_name = 'user_table'

so as to avoid any transcription errors.

以免出现任何转录错误。