I think It's easier to use *= on SQL server than LEFT OUTER JOIN and =* rather than RIGHT OUTER JOIN, tidier to.
SELECT roles.roleid, roles.rolename, userrole.userid FROM roles, userrole WHERE roles.roleid *= userrole.roleid AND userrole.userid = 84 OR userrole.userid IS NULL Russ -----Original Message----- From: Spike [mailto:[EMAIL PROTECTED]] Sent: 29 January 2003 17:59 To: [EMAIL PROTECTED] Subject: RE: [ cf-dev ] SQL: Completely half asleep And another one with the userid column instead of a second roleid column. sheesh! SELECT roles.roleid, roles.rolename, userrole.userid FROM roles LEFT OUTER JOIN userrole ON roles.roleid = userrole.roleid WHERE userrole.userid = 84 OR userrole.userid IS NULL Spike Stephen Milligan Team Macromedia - ColdFusion Co-author 'Reality Macromedia ColdFusion MX: Intranets and Content Management' http://spikefu.blogspot.com > -----Original Message----- > From: Spike [mailto:[EMAIL PROTECTED]] > Sent: 29 January 2003 17:50 > To: [EMAIL PROTECTED] > Subject: RE: [ cf-dev ] SQL: Completely half asleep > > > 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 > > 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 17:45 > > To: [EMAIL PROTECTED] > > Subject: RE: [ cf-dev ] SQL: Completely half asleep > > > > > > > > > > SELECT role.roleid, role.rolename, userrole.userID > > > FROM Role AS role > > > INNER JOIN userrole AS userrole ON role.roleid = userrole.roleid > > > WHERE role.applicationid = 39 AND > > userrole.userID = 84 > > > > > > > It gives me what I could always get (read first email) which > > is everything > > that links both, BUT it doesn't give me every role. > > > > Have I stumped everyone on this list? > > > > 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] > > > -- ** 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]
