帮我找出此查询中的问题 [英] help me to figure out problem in this query

问题描述

我有三个表(角色，用户和组)，我想根据角色对登录页面进行身份验证.

首先，我使用一个具有用户名，密码和角色的表进行操作，并以

I have three tables,(roles, users and groups) and I want to authenticate my login page according to the role.

Firstly I do it with one table, having user name, password and role and run the query as

sCommandText = "SELECT * FROM users where username = '" + TextBox1.Text + "' AND password = '" + TextBox2.Text+ "'"; 

代码成功运行.

然后，我尝试使用这三个表.
表用户"具有属性userid，用户名和密码.
表组"具有组名，例如admin，用户，管理员等以及groupid.
表角色"具有用户标识和组标识.

我试图运行thr查询

The code runs successfully.

Then I tried doing it with the three tables.
Table ''users'' has the attributes userid, username and password.
Table ''groups'' has groupname such as admin, user, managers etc and groupid.
Table ''roles'' has userid and groupid.

I tried to run thr query

sCommandText = "SELECT users.username, users.password, groups.name FROM users INNER JOIN groups  ON roles.groupid = groups.groupid INNER JOIN users  ON roles.userid = users.userid Where U.username = '"+TextBox1+"' and U.password='"+TextBox2.Text+"'";

我收到一条错误消息.

有什么问题吗?

错误是

I get an error message.

What is the problem?

The error is

The multi-part identifier "roles.groupid" could not be bound.<br />
The objects "Users" and "users" in the FROM clause have the same exposed names. Use correlation names to distinguish them. 

请帮助

Please help

推荐答案

根据这行FROM users INNER JOIN groups ON roles.groupid = groups.groupid，您要加入两个表的用户和组.但是，在尝试在groupid上进行联接时，您使用了名为roles的第三个表.结果，此查询失败.

尝试分别对表roles进行联接.

As per this line FROM users INNER JOIN groups ON roles.groupid = groups.groupid you are joining on two tables users and groups. However, you have used a third table called roles while trying to join on groupid. As a result, this query fails.

Try and join on table roles separately.

您正在从用户中进行选择，然后内部加入用户，请尝试这样的事情

SELECT users.username，users.[password]，groups.name FROM users
角色内的INNER JOIN角色.userid= users.userid
INNER JOIN将角色分组在role.groupid = groups.groupid
其中U.username =''"+ TextBox1 +"和U.password =""+ TextBox2.Text +"'';

希望这会有所帮助:)

Hi,

You are selecting from users and then inner joining on users, try somthing like this this

SELECT users.username, users.[password], groups.name FROM users
INNER JOIN roles ON roles.userid = users.userid
INNER JOIN groups ON roles.groupid = groups.groupid
Where U.username = ''"+TextBox1+"'' and U.password=''"+TextBox2.Text+"''";

hope this helps :)

这篇关于帮我找出此查询中的问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！