使用inner join實現多表串連查詢

查詢四張表aspnet_Membership，aspnet_Users，Users，UserRoles。

1.不是用inner join四表串連查詢

declare @username nvarchar(100)<br />declare @userpwd nvarchar(100)<br />set @username='admin'<br />set @userpwd='password'<br />SELECT * FROM aspnet_Membership aM, aspnet_Users aU,Users U,UserRoles UR where aM.UserId=aU.UserId and<br />aU.UserName=U.Username and U.UserID=UR.UserID<br />and U.Username=@username and aM.password=@userpwd and UR.roleid=0

2.使用inner join四表串連查詢

declare @username nvarchar(100)<br />declare @userpwd nvarchar(100)<br />set @username='admin'<br />set @userpwd='password'<br />select * from (((aspnet_Membership INNER JOIN aspnet_users ON aspnet_Membership.UserId = aspnet_users.UserId)<br />INNER JOIN Users ON aspnet_users.username=Users.username)INNER JOIN UserRoles ON Users.userid=UserRoles.userid)<br />where Users.Username=@username and aspnet_Membership.password=@userpwd and UserRoles.roleid=0

 

使用上面的查詢語句可以查詢指定的username和password並且角色roleid=0的結果。