Spike, > um... > > I haven't been paying attention to this so far, but from > reading the original email wouldn't this do it? > > SELECT roles.roleid, roles.rolename, users.roleid > FROM roles LEFT OUTER JOIN users ON roles.roleid = > users.roleid WHERE users.userid = 84 > > Spike
Not quite. I'm slightly intrigued by the problem now, and want to find a solution. Basically what I want is a recordset that contains two things. 1) a list of ALL the possible roles 2) a flag (or something) to say whether the current user has that role So effectively something like this: Role Rolename userid A role a 44 B role b NULL C role c 44 Which I hope you can see has all the information necessary! Now the tables this is being done on: Role ---- RoleID (int) PK RoleName (varchar) ApplicationID (int) (FK Application table) UserRole -------- UserRoleID (int) PK RoleID (int) (FK Role table) UserID (int) (FK User table) - and if anyone points out it shouldn't be user, but [User] we know! So, it's trivial to get the data we want from the tables even using a basic query like this: SELECT Role.RoleID, Role.RoleName FROM Role, UserRole WHERE Role.RoleID = UserRole.RoleID AND UserRole.UserID = 39 AND Role.ApplicationID = 84 Or using the query you suggested at the top of the email. Same result. HOWEVER What I want, is some way of getting a resultset that contains ALL the roles for that application WITH the userid (or a flag) OR NULL as to whether that user has an entry for that Role in the UserROle table. Can anyone figure it out in SQL? I know it's trivial in code (just do loops and checking) but imagine if there were many many roles... Could take a while (and it doesn't need to). Anyone at all? Paul -- ** Archive: http://www.mail-archive.com/dev%40lists.cfdeveloper.co.uk/ To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] For human help, e-mail: [EMAIL PROTECTED]
