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.
| Author |
Topic |
|
tiwas
Starting Member
37 Posts |
 Posted - 2009-12-22 : 05:57:11
|
| Hi all,I have made a few different tables in my db:* Roles* RolesTable* Users (in addition to aspnet_users - for storing more details about the users)* Companies...plus some more, but they're not vital to thisIn Roles, I have the following fields:* UserID* CompanyID* RoleIDThe point here is that a user can have a role in more than one company, and at least more roles inside the company. To list this, before filtering, I tried something like:Select *From Roles LEFT OUTER JOIN RolesTable ON Roles.RoleID = RolesTable.RoleIDLEFT INNER JOIN Users ON Users.UserID = Roles.UserIDLEFT INNER JOIN Companies ON Roles.CompanyID = Companies.CompanyIDThis falls flat on its stomack...Any good tips about WHY? I just need to create a long list with all the rows from Roles, and then fill out the info from the other tables. Then, later on, I will need to select a subset based on the company doing the listing, but that should be as easy as adding a WHERE Companies.CompanyID = currentCompany or something like that.Any help would be greatly appreciated!Cheers :) |
|
|
RickD
Slow But Sure Yak Herding Master
3608 Posts |
Posted - 2009-12-22 : 06:19:54
|
| Because there is no such thing as a LEFT INNER JOIN. Just use LEFT JOIN. |
 |
|
|
tiwas
Starting Member
37 Posts |
Posted - 2009-12-22 : 08:25:56
|
| Thanks!This worked:SELECT *FROM UsersInRoles LEFT OUTER JOIN aspnet_Users ON aspnet_Users.UserId = UsersInRoles.userID LEFT JOIN RoleTable ON UsersInRoles.roleID = RoleTable.RoleID LEFT JOIN Company ON UsersInRoles.companyID = Company.IDMust've been a REAL smelly brain fart...;) |
|
|
|
|
|
|
outer left join?