Select statement - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Select statement

Author

Topic

dr223
Constraint Violating Yak Guru

444 Posts

Posted - 2011-08-25 : 07:33:19

Hi,

I have the following query which works fine but does neglects some data which I want to be output.

N/B: I'LL consider three tables out of the 5 tables in the query as I am sure thats where the problem is..

The 3 tables are :

1) aspnet_users
Userid
1
2
3
4

2) aspnet_UsersinRoles
Userid Roleid
1 100
2 102

3) aspnet_Roles
Roleid Rolename
100 Manager
102 Supervisor

The query is as shown;

SELECT     dbo.aspnet_UserInfo.FirstName, dbo.aspnet_UserInfo.LastName, dbo.aspnet_Users.UserName, dbo.aspnet_Membership.Email, 
                      dbo.aspnet_Roles.RoleName, dbo.aspnet_Membership.Comment, dbo.aspnet_Users.UserId
FROM         dbo.aspnet_Membership INNER JOIN
                      dbo.aspnet_UserInfo ON dbo.aspnet_Membership.UserId = dbo.aspnet_UserInfo.UserId INNER JOIN
                      dbo.aspnet_Users ON dbo.aspnet_Membership.UserId = dbo.aspnet_Users.UserId AND 
                      dbo.aspnet_UserInfo.UserId = dbo.aspnet_Users.UserId INNER JOIN
                      dbo.aspnet_UsersInRoles ON dbo.aspnet_Users.UserId = dbo.aspnet_UsersInRoles.UserId INNER JOIN
                      dbo.aspnet_Roles ON dbo.aspnet_UsersInRoles.RoleId = dbo.aspnet_Roles.RoleId

Therefore, what I want to do is the query to output all the users in aspner_Users with Rolenames

Therefore, I'll output as (more fields will be displayed but am concentrating on ONLY these 2 for simplicity of the issue)

Userid Rolename
1 Manager
2 Supervisor
3 NULL
4 NULL

Currently, it gives me

Userid Rolename
1 Manager
2 Supervisor

Neglecting the UserId with NULL Rolenames..

Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-08-25 : 07:36:38

[code]
SELECT dbo.aspnet_UserInfo.FirstName, dbo.aspnet_UserInfo.LastName, dbo.aspnet_Users.UserName, dbo.aspnet_Membership.Email,
dbo.aspnet_Roles.RoleName, dbo.aspnet_Membership.Comment, dbo.aspnet_Users.UserId
FROM dbo.aspnet_Membership INNER JOIN
dbo.aspnet_UserInfo ON dbo.aspnet_Membership.UserId = dbo.aspnet_UserInfo.UserId INNER JOIN
dbo.aspnet_Users ON dbo.aspnet_Membership.UserId = dbo.aspnet_Users.UserId AND
dbo.aspnet_UserInfo.UserId = dbo.aspnet_Users.UserId LEFT JOIN
dbo.aspnet_UsersInRoles ON dbo.aspnet_Users.UserId = dbo.aspnet_UsersInRoles.UserId LEFT JOIN
dbo.aspnet_Roles ON dbo.aspnet_UsersInRoles.RoleId = dbo.aspnet_Roles.RoleId

[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

dr223
Constraint Violating Yak Guru

444 Posts

Posted - 2011-08-25 : 07:43:00

[code]SELECT dbo.aspnet_UserInfo.FirstName, dbo.aspnet_UserInfo.LastName, dbo.aspnet_Users.UserName, dbo.aspnet_Membership.Email,
dbo.aspnet_Roles.RoleName, dbo.aspnet_Membership.Comment, dbo.aspnet_Users.UserId
FROM dbo.aspnet_Roles INNER JOIN
dbo.aspnet_UsersInRoles ON dbo.aspnet_Roles.RoleId = dbo.aspnet_UsersInRoles.RoleId RIGHT OUTER JOIN
dbo.aspnet_Membership INNER JOIN
dbo.aspnet_UserInfo ON dbo.aspnet_Membership.UserId = dbo.aspnet_UserInfo.UserId INNER JOIN
dbo.aspnet_Users ON dbo.aspnet_Membership.UserId = dbo.aspnet_Users.UserId AND dbo.aspnet_UserInfo.UserId = dbo.aspnet_Users.UserId ON
dbo.aspnet_UsersInRoles.UserId = dbo.aspnet_Users.UserId
[/code]

This also gave me similar results - does it mean th etwo queries are the same?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-08-25 : 07:44:21

you mean one i posted and yours or both your queries?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

dr223
Constraint Violating Yak Guru

444 Posts

Posted - 2011-08-25 : 07:54:13

yeah one you posted and mine - they gave similar results
Notice: I used an RIGHT OUTER Join

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-08-25 : 07:59:04

they're not similar. your usage of RIGHT JOIN will ensure all records from right part of join are included along matching value from left part if present else it NULL. My query takes all records from left part and then return it regardless of match on right side. wherever it has match it will return column values otherwise NULL

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

dr223
Constraint Violating Yak Guru

444 Posts

Posted - 2011-08-25 : 08:13:46

thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-08-25 : 12:37:24

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources