asp.net SelectCommand SQL JOIN问题

发布于 2024-10-03 17:48:25 字数 752 浏览 5 评论 0原文

我是一个数据库菜鸟，所以请耐心等待。我试图在 SqlDataSource 的 SelectCommand 属性中将三个表连接在一起。这是我现在的选择命令：

SelectCommand="SELECT DISTINCT a1.[CreateDate], a2.[UserName], c1.[name], c1.[organisation], c1.[address], a1.[IsApproved], a1.[UserId] FROM [aspnet_Membership], [aspnet_Users] a2, [clients] c1 INNER JOIN [aspnet_Membership] a1 ON c1.userid = a1.UserId INNER JOIN [clients] c2 ON c2.userid = a2.UserId AND a1.IsApproved = 'False'"

它有点脏，因为我一直在玩它。我试图显示来自 aspnet_Membership (CreateDate) 的注册日期、来自 aspnet_Users (UserName) 的用户名以及我的自定义客户端表中的其他详细信息。但我的加入不起作用。我设法让 aspnet_Membership 与客户端正常加入以显示 CreateDate，但我无法加入 aspnet_Users 以显示用户名。我不断收到错误消息“aspnet_Users.UserId 无法绑定”。

有什么想法吗？我很确定问题出在连接上，但无论我对命令进行多少调整，我都无法得到任何结果。

任何帮助将不胜感激:)

原文

I'm a database noob so please bear with me. I'm trying to join three tables together in the SelectCommand attribute of a SqlDataSource. Here's my select command at the moment:

SelectCommand="SELECT DISTINCT a1.[CreateDate], a2.[UserName], c1.[name], c1.[organisation], c1.[address], a1.[IsApproved], a1.[UserId] FROM [aspnet_Membership], [aspnet_Users] a2, [clients] c1 INNER JOIN [aspnet_Membership] a1 ON c1.userid = a1.UserId INNER JOIN [clients] c2 ON c2.userid = a2.UserId AND a1.IsApproved = 'False'"

It's kinda mucky because I've been playing with it. I'm trying to display the registered date from aspnet_Membership (CreateDate), username from aspnet_Users (UserName) and other details from my custom clients table. But my joins aren't working. I managed to get aspnet_Membership to join fine with clients to display CreateDate, but I can't join aspnet_Users to show the username. I keep getting errors saying "aspnet_Users.UserId could not be bound".

Any ideas? I'm pretty sure the problem is in the joining, but not matter how many tweaks I make to the command I can't get any results.

Any help would be much appreciated :)

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

甜味拾荒者 2024-10-10 17:48:25

为什么你要使用两次客户端？（也许解释一下你想要实现的目标）

试试这个：

SELECT DISTINCT 
    am.[CreateDate], ru.[UserName], cl.[name], cl.[organisation], 
    cl.[address], am.[IsApproved], am.[UserId] 
FROM 
    [aspnet_Membership] am
    INNER JOIN clients cl
        ON am.userid = cl.userid
    INNER JOIN [RolesAndAllUsers] ru
        ON cl.userid = ri.userid
WHERE
    cl.IsApproved = 'False'

Why are you using clients twice? (Maybe explain what you're trying to achieve)

Try this:

SELECT DISTINCT 
    am.[CreateDate], ru.[UserName], cl.[name], cl.[organisation], 
    cl.[address], am.[IsApproved], am.[UserId] 
FROM 
    [aspnet_Membership] am
    INNER JOIN clients cl
        ON am.userid = cl.userid
    INNER JOIN [RolesAndAllUsers] ru
        ON cl.userid = ri.userid
WHERE
    cl.IsApproved = 'False'

回复收藏 0 原文

a√萤火虫的光℡ 2024-10-10 17:48:25

这就是您发布的内容。我怀疑交叉连接是你的问题所在。

SELECT DISTINCT a1.[CreateDate], a2.[UserName], c1.[name], c1.[organisation], 
    c1.[address], a1.[IsApproved], a1.[UserId] 
FROM [aspnet_Membership]
CROSS JOIN [RolesAndAllUsers] a2
CROSS JOIN [clients] c1 
INNER JOIN [aspnet_Membership] a1 ON c1.userid = a1.UserId 
INNER JOIN [clients] c2 ON c2.userid = a2.UserId 
WHERE a1.IsApproved = 'False'

This is what you have posted. I suspect the cross joins are where your problem lies.

SELECT DISTINCT a1.[CreateDate], a2.[UserName], c1.[name], c1.[organisation], 
    c1.[address], a1.[IsApproved], a1.[UserId] 
FROM [aspnet_Membership]
CROSS JOIN [RolesAndAllUsers] a2
CROSS JOIN [clients] c1 
INNER JOIN [aspnet_Membership] a1 ON c1.userid = a1.UserId 
INNER JOIN [clients] c2 ON c2.userid = a2.UserId 
WHERE a1.IsApproved = 'False'

回复收藏 0 原文

~没有更多了~