我试图加入一些表以接收一些数据,但它没有按预期工作。这是我的查询:
SELECT USR.PK_User AS PKUser
,USR.NAME AS NAME
FROM [User] USR
LEFT JOIN aspnet_Users ASPU ON USR.NAME = ASPU.UserName
LEFT JOIN aspnet_UsersInRoles ASPUIR ON ASPU.UserId = ASPUIR.UserId
WHERE ASPUIR.RoleId = 'E8700479-902A-42F1-A500-4FA02CAC356C'
此查询未返回任何内容。但是,如果我这样做:
SELECT * FROM [aspnet_UsersInRoles] WHERE RoleId = 'E8700479-902A-42F1-A500-4FA02CAC356C'
我得到76个选定的行。如果我只是这样做:
SELECT USR.PK_User AS PKUser
,USR.NAME AS NAME
FROM [User] USR
LEFT JOIN aspnet_Users ASPU ON USR.NAME = ASPU.UserName
LEFT JOIN aspnet_UsersInRoles ASPUIR ON ASPU.UserId = ASPUIR.UserId
我得到了预期的行返回。另外,如果我添加:
WHERE ASPUIR.RoleId IS NULL
我回来了。
我究竟做错了什么?
您需要的ON
不是条件WHERE
。
否则,您只能使其有效地返回到内部联接中。
SELECT USR.PK_User AS PKUser,
USR.NAME AS NAME
FROM [User] USR
LEFT JOIN aspnet_Users ASPU
ON USR.NAME = ASPU.UserName
LEFT JOIN aspnet_UsersInRoles ASPUIR
ON ASPU.UserId = ASPUIR.UserId
AND ASPUIR.RoleId = 'E8700479-902A-42F1-A500-4FA02CAC356C'
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句