Yeah, I noticed that.
The third one I sent should work though. Spike Stephen Milligan Team Macromedia - ColdFusion Co-author 'Reality Macromedia ColdFusion MX: Intranets and Content Management' http://spikefu.blogspot.com > -----Original Message----- > From: Paul Johnston [mailto:[EMAIL PROTECTED]] > Sent: 29 January 2003 18:17 > To: [EMAIL PROTECTED] > Subject: RE: [ cf-dev ] SQL: Completely half asleep > > > 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] > > > -- ** 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]
